Part 5. Working with
Formulas and Functions
In Excel, a formula calculates a value based on the values in other cells of the workbook. Excel displays the result of a formula in a cell as a numeric value.
Functions are abbreviated formulas that perform a specific operation on a group of values. Excel provides more than 250 functions that can help you with tasks ranging from determining loan payments to calculating investment returns. For example, the SUM function automatically adds entries in a range. To use it, you first type =SUM( in either lower- or uppercase letters. Then you select the range. You end the function by typing ), which tells Excel you are finished selecting the range.
The way you refer to a cell in a formula determines how the formula is affected when you copy it into a different cell. You can use three types of cell references: relative, absolute, and mixed. The formulas you create in this part contain relative cell references. When you copy a formula from one cell to another, the relative cell references in the formula change to reflect the new location of the formula.
An absolute cell reference does not change when you copy the formula to a new cell. In certain formulas, you might want an entry to always refer to one specific cell value. For example, you might want to calculate the interest on several different principal amounts. The interest percentage remains unchanged, or absolute, so you designate the entry in the formula that refers to the interest percentage as an absolute cell reference. The principal amounts do change, so they have relative cell reference entries in the formula. When you copy this formula, the interest cell reference always refers to the one cell that contains the interest percentage.
A mixed cell reference is a single cell entry in a formula that contains both a relative and an absolute cell reference. A mixed cell reference is helpful when you need a formula that always refers to the values in a specific column but the values in the rows must change, and vice versa.
Functions and Correcting
Errors
Using AutoSum (SUM)
Click in the cell in which you
want the result of the AutoSum operation to appear (this is called the resultant cell).
Click the AutoSum button on the Standard toolbar.
Excel selects the most obvious
range of numbers and puts a dotted line around the cells. Press Enter to accept the range or use the mouse to
select alternative cells.
Click the resultant cell to
make it the active cell. Notice that the formula is displayed in the Formula
bar.
INTRODUCTION
Excel can use formulas to perform calculations for you. Because a formula refers to cells rather than to the values those cells contain, Excel updates the sum whenever you change the values in the cells. You'll probably use the AutoSum formula a lot—it adds numbers in a range of cells. |
TIP
Selecting specific AutoSum cells If you don't want to use the range of cells that Excel selects for you, click on the first cell you want, hold down the Ctrl key, and click on each additional cell you would like to include in the calculation. When you finish selecting the cells you want to calculate, press Enter to see the result. Alternatively, if you let Excel select the cells for you but Excel doesn't select exactly the right set of cells, you can resize the selection by clicking the first cell to include, holding down the Shift key, and clicking the last cell to include. |
Finding a Cell Average
(AVERAGE)
Click the cell in which you
want the result of the AVERAGE function to
appear (this is called the resultant cell).
Click the down arrow next to the AutoSum
button on the Standard toolbar and choose Average
from the list that appears.
Excel selects the most obvious
range of numbers and puts a dotted line around the cells. Press Enter to accept the range or use the mouse to
select alternative cells.
Click the resultant cell to
make it the active cell. Notice that the formula is displayed in the Formula
bar.
INTRODUCTION
A function is one of Excel's many built-in formulas for performing a specialized calculation on the data in your worksheet. For example, instead of totaling your sales data, you can use Excel's AVERAGE function to determine the average of each quarter per region. |
TIP
Selecting specific AutoSum cells If you don't want to use the range of cells that Excel selects for you, click on the first cell you want, hold down the Ctrl key, and click on each additional cell you would like to include in the calculation. When you finish selecting the cells you want to calculate, press Enter to see the result. Alternatively, if you let Excel select the cells for you but Excel doesn't select exactly the right set of cells, you can resize the selection by clicking the first cell to include, holding down the Shift key, and clicking the last cell to include. |
Finding the Largest Cell
Amount (MAX)
Click the cell in which you
want the result of the MAX function to appear
(this is called the resultant cell).
Click the down arrow next to the AutoSum
button on the Standard toolbar and choose Max
from the list that appears.
Excel selects the most obvious
range of numbers and puts a dotted line around the cells. Press Enter to accept the range or use the mouse to
select alternative cells.
Click the resultant cell to
make it the active cell. Notice that the formula is displayed in the Formula
bar.
INTRODUCTION
You can use Excel's MAX function to, for example, determine the quarter in which you had the most sales. |
TIP
Finding the minimum cell amount In addition to enabling you to find the largest cell amount, you can also find the smallest cell amount. See the task "Finding the Smallest Cell Amount (MIN)" later in this part for more information. |
Counting the Number of
Cells (COUNT)
Click the cell in which you
want the result of the COUNT function to
appear (this is called the resultant cell).
Click the down arrow next to the AutoSum
button on the Standard toolbar and choose Count
from the list that appears.
Excel selects the most obvious
range of numbers and puts a dotted line around the cells. Press Enter to accept the range or use the mouse to
select alternative cells.
Click the resultant cell to
make it the active cell. Notice that the formula is displayed in the Formula
bar.
INTRODUCTION
Suppose you want to know how many sales reps are associated with a specific region. In that case, you could use Excel's COUNT function to count the number of cells in a selected range. |
TIP
COUNTIF You can use the COUNTIF function to count the number of cells in a range that meet specific criteria. For example, instead of totaling your sales data, maybe you want to know how many regional quarters were under $20,000. |
Finding the Smallest Cell
Amount (MIN)
Click the cell in which you
want the result of the function to appear (this is called the resultant cell).
Click the down arrow next to the AutoSum
button on the Standard toolbar and choose Min from the list that appears.
Excel selects the most obvious
range of numbers and puts a dotted line around the cells. Press Enter to accept the range or use the mouse to
select alternative cells.
Click the resultant cell to
make it the active cell. Notice that the formula is displayed in the Formula
bar.
INTRODUCTION
Suppose you want to know in which quarter you had the lowest sales. In that case, you could use Excel's MIN function, and thereby discover which regions need improvement. |
TIP
Finding the maximum cell amount In addition to enabling you to find the smallest cell amount, you can also find the largest cell amount. Refer to the task "Finding the Largest Cell Amount (MAX)" earlier in this part for more information. |
Entering a Formula
Click the cell in which you want
the result of the formula to appear (this is called the resultant cell).
Type = (the equal sign) followed by the references of
the cells containing the data you want to total (for example, G4+G5+G6+G7+G8), and then press Enter.
Click the resultant cell to
make it the active cell; the values in the specified cells are added together.
INTRODUCTION
Another way to use a formula is to type it directly into the cell. You can include any cells in your formula; they do not have to be next to each other. Also, you can combine mathematic operations—for example, C3+C4–D5. |
TIP
Canceling a formula If you start to enter a formula and then decide you don't want to use it, you can cancel it by pressing the Esc key. |
TIP
Order of operation Excel first performs calculations within parentheses. Then it performs multiplication or division calculations from left to right. Finally, it performs any addition or subtraction from left to right. |
Editing a Formula or
Function
Click the cell you want to
edit; the function is displayed in the Formula bar.
Click the Insert Function button on the Formula bar to open
the Function Arguments dialog box. (If a formula, the Insert Function dialog
box will appear.)
Type the changes to your
function. For example, change the cells being summed to B4–B8 instead of B4–B9.
Then click OK.
The changes are made and the
result appears in the cell.
INTRODUCTION
After you enter a formula or function, you can change the values in the referenced cells, and Excel automatically recalculates the value based on the changes. You can include any cells in a formula or function; they do not have to be next to each other. Also, you can combine mathematical operations—for example, C3+C4–D5. |
TIP
Pressing F2 Instead of using the Function Arguments dialog box to edit your formulas, you can press the F2 key and edit your formula just like you would regular text or data on the Formula bar. |
Copying a Formula
Click the cell that contains
the function you want to copy.
Click the Copy button on the Standard toolbar; a line
surrounds the cell you are copying.
Click the cell or cells into
which you want to paste the formula.
Press Enter to paste the formula into each of the
selected cells.
INTRODUCTION
When you build your worksheet, you might want to use the same data and formulas in more than one cell. With Excel's Copy command, you can create the initial data or formula once and then place copies in the appropriate cells. For example, suppose you want to find the average sales per quarter in other sales regions. To do so, create the formula for the first region, and copy it to cells for the other regions. |
TIP
Increasing cell width If you paste a copied formula, you might need to alter the size of your columns to accommodate the new size of the data in the cell. To automatically make an entire column (or multiple columns) fit the width of the widest cell in that column (or columns), move the cursor over the right side of the column header and double-click when the cursor changes to a two-headed arrow. |
Assigning Names to a Cell
or Range
Select the cell or range you
want to name.
Open the Insert menu, choose Name,
and select Define to open the Define Name
dialog box, which displays the range coordinates and suggests a name.
If you do not like the name
Excel suggests, type a new name. When you are satisfied with the name, click OK.
Excel names the range. When
the range is selected, the name appears in the Name box.
INTRODUCTION
You can create range names that make it easier to create formulas and move to that range. For example, a formula that refers to a range named Qtr1 is easier to understand than one named B4:B9. Not only is it easier to remember a name than the cell addresses, but Excel also displays the range name in the Name box—next to the Formula bar. You can name a single cell or a selected range in the worksheet. |
TIP
Naming ranges It's a good idea to begin the range name with a letter or underscore character. You can include upper- and lowercase letters, and you can include as many as 255 characters. Don't use a range name that looks like a cell reference and don't include spaces. To go to a named range, open the Edit menu and choose Go To. Click on the range name in the list, and then click OK. |
Referencing Names in a
Function
Click in the cell in which you
want the result of the formula to appear (this is called the resultant cell).
Type the function in the cell
using a named cell or range—for example, =AVERAGE(January).
The name appears in the same color as the border.
Press the Enter key.
INTRODUCTION
One of the reasons you create a name for a cell or group of cells is so that you can easily refer to that cell or range in a function. That way, rather than typing or selecting a range or cell, you can type the name or select it from the Paste Name dialog box. |
TIP
Pasting the name If you forget the name of a range while you are typing a formula, open the Insert menu, choose Name, select Paste, choose the correct range name, and click OK. The range name is automatically placed in the formula. |
TIP
Deleting a name To delete a name, open the Insert menu, choose Name, and select Define. In the Define Name dialog box, select the range you want to delete, and then click the Delete button. Click OK to confirm the deletion. |
Using Functions Across
Worksheets
Click in the cell in which you
want the result of the formula to appear (this is called the resultant cell).
Click the AutoSum button on the Standard toolbar. Excel
selects the logical cells to sum.
In this example, we are going
to add the sum of this operation to last year's data in another worksheet
(2003), so type a + outside the right
bracket.
Click the tab of the worksheet
that contains the cell you want to reference in the calculation.
INTRODUCTION
You can use cell references from other worksheets in your calculations. For example, suppose you have two worksheets that contain the calculations for the total sales by region for a particular year. In a third worksheet, you want to calculate the total sales by region for the last two years. You can reference the cells in the first two worksheets that contain the totals and perform calculations on them in the third worksheet. |
TIP
Worksheet name references Instead of switching back and forth between worksheets, you can reference the worksheet name in your calculations. The location of a cell in a particular worksheet is the cell row and column location in addition to the sheet name—for example, Sheet1!A1. |
Click the cell that you want
to use in your calculation; it appears next to the worksheet name in the
Formula bar.
Press the Enter key. Excel performs the calculation and
returns you to the original worksheet.
Click the resultant cell to
make it the active cell. Notice that the function is displayed in the Formula
bar.
CAUTION
If you only reference one worksheet name in a formula, the cells referenced in the formula will all apply to the one referenced worksheet. For example, if you have the formula =SUM(Sheet1!A1+B1) in cell C3 of Sheet2, this will reference cell A1 from Sheet1 and B1 from Sheet1. If you need to reference a cell in another worksheet, you must include the worksheet name in the formula, like so: =SUM(Sheet1!A1+Sheet2!B1). |
Using AutoCalculate
Select the cells that you want
to AutoCalculate.
Right-click the Status bar and
review the AutoCalculate options.
To find the lowest number in
the selection, click Min in the shortcut menu
that appears. (Notice that the default AutoCalculate operation is to sum the
numbers.)
The Status bar displays the
lowest number in the selection (in this case, $18,000.00).
INTRODUCTION
Suppose you want to see a function performed on some of your data—in this example, to determine the lowest quarterly sales goal of any region in 2004—but you don't want to add the function directly into the worksheet. Excel's AutoCalculate feature can help. |
TIP
Turning off AutoCalculate You can turn off the AutoCalculate feature by selecting None from the AutoCalculate shortcut menu. Otherwise, it continues to display when you select cells. |
TIP
AutoCalculate options In addition to using AutoCalculate to find a minimum number, you can also select cells to be averaged (Average), count the total number of cells containing data (Count), count the total number of cells containing numeric data (Count Nums), find the largest number (Max), and sum a total (Sum). |
Finding Functions
Click the down arrow next to the AutoSum
button on the Standard toolbar and choose More
Functions from the list that appears.
Type a description of the
function you are looking for in the Search for a
function text box and press Enter (or
click the Go button).
Scroll through the list in the
Select a function box and click a function to
read a description of it. When you find the function you are looking for, click
OK.
Excel walks you through the
process of inputting the function's arguments in the Function Arguments dialog
box.
INTRODUCTION
In the old days, you had to know the name of the function you wanted to use. Now, however, Excel makes it easy to find the function you need—all you have to know is what you want the function to do. |
TIP
Function arguments help If you need help while you are inputting your function arguments, click the Help on this function link in the bottom-left corner of the Function Arguments dialog box. |
Calculating a Loan Payment
(PMT)
Click in the cell in which you
want the result of the function to appear (this is called the resultant cell).
Click the down arrow next to the AutoSum
button on the Standard toolbar and choose More
Functions from the list that appears.
The Insert Function dialog box
opens. Click the down arrow next to the Or select a category field and choose Financial from the list that appears.
A list of financial-related
functions appears in the Select a function
list. Scroll through the list to locate the PMT
function, and double-click it.
INTRODUCTION
Using Excel, you can determine a monthly loan payment based on a constant interest rate, a specific number of pay periods, and the current loan amount. |
TIP
Function arguments help If you need help while you are inputting your function arguments, click the Help on this function link in the bottom-left corner of the Function Arguments dialog box. |
In the Rate field, type the interest rate per period. For
example, type 6%/12 for monthly payments on a
6% annual percentage rate (APR).
In the Nper field, type the total number of loan payments.
For example, type 360 if you'll be making 12
payments per year on a 30-year loan.
In the Pv field, type the present value of the loan—for
example, 150,000. Click OK.
Excel calculates the payment
and inserts it in the resultant cell.
TIP
Negative payment Your resulting payment is a negative number because payments are considered a debit. |
TIP
Type argument If you don't enter a number in the Type argument, it defaults to 0, which means that the last payment will pay off the mortgage loan (this is because mortgages are paid in arrears—at the end of the payment period). If you are calculating a car payment, you might put a 1 in the Type argument because you make your payments at the beginning of the payment period. A 1 versus a 0 makes a slight difference in the calculation because of the interest accrued. |
Performing a Logical Test
Function (IF)
Click in the cell in which you
want the result of the function to appear (this is called the resultant cell).
Click the down arrow next to the AutoSum
button on the Standard toolbar and choose More
Functions from the list that appears.
The Insert Function dialog box
opens. Click the down arrow next to the Or select a category field and choose Logical from the list that appears.
A list of logical functions
appears in the Select a function list. Scroll
through the list to locate the IF function,
and double-click it.
INTRODUCTION
Using Excel, you can perform a logical test function—for example, to indicate whether scores equal a passing or failing grade based on an established set of criteria. |
TIP
Embedded IFs You can set up embedded IF statements to use in your logical test. For example, suppose scores between 90–100 are an A, 80–89 are a B, 70–79 are a C, 60–69 are a D, and below 59 are an F. Your formula might look like this: =IF(B3>89, "A", IF(B3>79, "B", IF(B3>69, "C", IF(B3>59, "D", "F"). |
In the Logical_test field, type the condition to determine
whether a grade is above 70. The logical test is if the cell is greater than
70, for example, B3>70.
In the Value_if_true field, type the value you want to use
if the grade is above 70 (that is, a passing grade)—for example, "Pass".
In the Value_if_false field, type the value you want to
use if the grade is below 70 (that is, a failing grade)—for example, "Fail". Click OK.
Excel performs the logical
test and inserts the result in the resultant cell.
TIP
Copying a logical test You can copy a logical test function in a cell and paste it so that you have the same function performed on all cells in a list—for example, if you need this pass/fail test performed on a set of student grades. |
TIP
Function arguments help If you need help while you are inputting your function arguments, click the Help on this function link in the bottom-left corner of the Function Arguments dialog box. |
Adding a Range Given
Criteria (SUMIF)
Click in the cell in which you
want the result of the function to appear (this is called the resultant cell).
Click the down arrow next to the AutoSum
button on the Standard toolbar and choose More
Functions from the list that appears.
The Insert Function dialog box
opens. Click the down arrow next to the Or select a category field and choose Math & Trig from the list that appears.
A list of math and trig
functions appears in the Select a function
list. Scroll through the list to locate the SUMIF
function, and double-click it.
INTRODUCTION
Using Excel, you can add the data in range, given certain criteria. This might be useful if, say, you needed to total the current monthly sales for all sales reps who match a specific criterion—for example, who are all in the same sales region. |
TIP
Function arguments help If you need help while you are inputting your function arguments, click the Help on this function link in the bottom-left corner of the Function Arguments dialog box. |
In the Range field, type the range of cells whose contents
you want to review—for example, C2:C79. (You
can also click directly in the worksheet to select the cells.)
In the Criteria field, type the criterion you want to
check in the range—in this case, type "1"
because you want to add sales data from Region 1 only.
In the Sum_range field, type the range of cells that match
your criterion—in this case, F2:F79, which
contains the current month sales data. Click OK.
Excel adds the range given
your criteria and inserts the result in the resultant cell.
TIP
Range selection In addition to typing specific Range and Sum_range arguments, you can click in the worksheet and select each range of cells using the mouse. |
TIP
COUNTIF Instead of totaling cells that meet a criterion with SUMIF, you can use COUNTIF to count the number of cells in a range that meet a specific criterion. For example, instead of totaling your sales data, maybe you want to know how many regional quarters were under $20,000. |
Finding the Future Value
of an Investment (FV)
Click in the cell in which you
want the result of the function to appear (this is called the resultant cell).
Click the down arrow next to the AutoSum
button on the Standard toolbar and choose More
Functions from the list that appears.
The Insert Function dialog box
opens. Click the down arrow next to the Or select a category field and choose Financial from the list that appears.
A list of financial-related
functions appears in the Select a function
list. Scroll through the list to locate the FV
function, and double-click it.
INTRODUCTION
If you open a 3% interest-bearing money market account with $100 in January, and make deposits of $100 each month, how much money will you have at the end of the year? Excel can help you calculate the future value of an amount of money, based on a constant interest rate, over a specific number of periods, in which you make a constant payment. |
TIP
Function arguments help If you need help while you are inputting your function arguments, click the Help on this function link in the bottom-left corner of the Function Arguments dialog box. |
In the Rate field, type the interest rate per period. For
example, type 3%/12 for monthly accrual on a
3% interest-bearing account.
In the Nper field, type the total number of payments for
the investment (in this example, 12
deposits).
In the Pmt field, type the amount to be paid in each
deposit—in this case, -100. Click OK.
Excel calculates the future
investment value and inserts it in the resultant cell.
TIP
Negative payments Because you are making a payment with each deposit, you need to make sure the Pmt argument is a negative value. |
Fixing the #### Error
If one of the cells in your
worksheet contains the #### error, click on the cell's column border and drag
it to increase the column width.
Drop the column border and the
error disappears, and the cell's actual data is displayed.
INTRODUCTION
Excel notifies you when there are errors in your data by displaying different error descriptions in the cell that contains the error. For example, when a cell contains the value ####, it means that the column that contains that value is not wide enough to display the actual data. Simply widen the column to see the cell's contents. |
TIP
Beginning with larger columns It is a good idea to start out with columns that are larger than you need. Then you can decrease their size while you are formatting the worksheet. |
TIP
AutoFitting column widths To automatically make an entire column (or multiple columns) fit the width of the widest cell in that column (or columns), move the cursor over the right side of the column header and double-click when the cursor changes to a two-headed arrow. |
Fixing the #DIV/0! Error
In the cell you want to use as
the resultant cell, type a formula to obtain an average, for example, type =SUM(B7/B6) in cell B9 and press Enter.
If one of the cells in your
worksheet now contains the #DIV/0! error,
locate the empty cell referenced by the formula (in this case, cell B6).
Click on the resultant cell
(here, B9), press F2
on the keyboard, and retype the formula to omit the empty cell—in this case, =(B7/B8)—and press Enter.
The error disappears because it
is no longer trying to divide a number by an empty cell.
INTRODUCTION
Excel notifies you when there are errors in your data by displaying different error descriptions in the cell that contains the error. For example, when a cell contains the #DIV/0! error, it means that the formula is trying to divide a number by 0, or by an empty cell. |
TIP
Pressing the Delete key In this example, you could also press the Delete key in cell B9 to remove the formula. |
Fixing the #NAME? Error
In the cell you want to use as
the resultant cell, type the formula you want to use in your calculation. For
example, type =CNT(B3:B5) in cell B8 and
press Enter.
In this case, you get the #NAME? error because CNT
is not the correct spelling for the referenced function; it is COUNT.
Click on the resultant cell
(here, B8), press F2
on the keyboard, and retype the formula—in this case, =COUNT(B3:B5)—and press Enter.
The error disappears because
the function is spelled correctly.
INTRODUCTION
Excel notifies you when there are errors in your data by displaying different error descriptions in the cell that contains the error. For example, when a cell contains a #NAME? error, it means the formula contains an incorrectly spelled cell or function name. |
TIP
Using the Paste Function dialog box The Paste Function dialog box offers many functions. Practice using different functions and see the results you get from your calculations. As you experiment, you can move the Paste Function dialog box around or collapse it to see your cells. |
Fixing the #VALUE! Error
In the cell you want to use as
the resultant cell, type the formula you want to use in your calculation. For
example, type =SUM(B2+B3+B4+B5) in cell B7, and press Enter.
In this case, you get the #VALUE! error because the value in cell B2 is
textual, not numeric. You won't get the error if you enter =SUM(B2:B5) instead of the plus signs.
Click on the resultant cell
(here, B7), press F2
on the keyboard, and retype the formula—in this case, =SUM(B3+B4+B5)—and press Enter.
The error disappears because
all cells are numeric.
INTRODUCTION
Excel notifies you when there are errors in your data by displaying different error descriptions in the cell that contains the error. For example, when a cell contains a #VALUE? value, it means the formula contains nonnumeric data or cell or function names that cannot be used in the calculation. |
TIP
Overwriting cells See the task "Overwriting and Deleting Data" in Part 3 to make sure you are overwriting data in cells correctly. |
Recognizing the #REF!
Error
In the cell you want to use as
the resultant cell, type the formula you want to use in your calculation. For
example, type =SUM(B3+C3+D3+E3) and press Enter.
Right-click one of the columns
that contains a cell referenced in the formula you just typed (here, column E), and choose Delete
from the shortcut menu that appears.
In this case, the #REF! error appears because the values in cells referenced
in column E are no longer available in the formula. Click the Undo button to correct it.
The error disappears because
the formula values are restored.
INTRODUCTION
Excel notifies you when there are errors in your data by displaying different error descriptions in the cell that contains the error. For example, when a cell contains a #REF! error, it means the formula contains a reference to a cell that isn't valid. Frequently, this means you deleted a referenced cell. The best solution is to undo your action and review the cells involved in the formula. |
TIP
Checking references If after you undo what caused the #REF! error you want to find out what caused it, see the tasks "Checking for Formula References (Precedents)" and "Checking for Cell References (Dependents)" later in this part for more information on checking formula and cell references. |
Recognizing Circular
References
Type the formula you want to
use in your calculation. For example, type =SUM(B4+C4+D4+E4+F4)
and press Enter.
Excel displays an error
message, notifying you that the formula contains a circular reference. Click OK.
Click on the resultant cell,
press F2 on the keyboard, and retype your
formula—in this case, =SUM(B4+C4+D4+E4)—and
press Enter.
The error is fixed because the
result cell no longer is in the calculation.
INTRODUCTION
Excel notifies you when there are errors in your data by displaying different error descriptions in the cell that contains the error. For example, you receive a circular reference error message when one of the cells you are referencing in your calculation is the cell in which you want the calculation to appear. |
TIP
Circular reference toolbar If you didn't intend to create a circular reference and you chose OK in the message box, the Circular Reference toolbar and Help will appear to assist you in correcting your actions. |
Checking for Formula
References (Precedents)
Open the Tools menu, choose Formula
Auditing, and select Show Formula Auditing
Toolbar.
Click the cell you want to
trace (this cell must contain a formula) and click the Trace Precedents button. Excel draws tracer arrows
to the appropriate cells.
Click the Trace Precedents button again to see if there are
any precedents for these calculations.
If additional precedents are
present, Excel draws additional tracer arrows to the appropriate cells.
INTRODUCTION
One way to check a formula to see if it is referencing the correct cells is to select that formula and then trace all cells that are referenced in that formula. Cells that are referenced are called precedents. |
TIP
Moving references You can double-click the arrows to move from one reference to another. Click the Remove Precedents Arrows button on the Auditing toolbar to remove the arrows. |
TIP
Closing the toolbar Close the Auditing toolbar by clicking the Close button (x) in the upper-right corner of the toolbar. |
Checking for Cell
References (Dependents)
Open the Tools menu, choose Formula
Auditing, and select Show Formula Auditing
Toolbar.
Click the cell you want to
trace. This cell must not contain a formula.
Click the Trace Dependents button until the tracer arrows
stop adding on to the appropriate cells.
Click the Remove Dependents Arrows button enough times to
remove all the arrows (or click the Remove All Arrows
button).
INTRODUCTION
When you trace a dependent, you start with a cell that is referenced in a formula and then trace all cells that reference this cell. This is another way to check if your formulas are correct. (If the cell is not referenced in a formula, you see an error message saying so.) |
TIP
Closing the toolbar Close the Auditing toolbar by clicking the Close button (x) in the upper-right corner of the toolbar. |
TIP
Using Trace Errors If the cell contains an error message, use the Trace Errors button to have Excel trace possible reasons for the error. |