Page 120 - Excel Workbook for Dummies

11_798452 ch06.qxp 3/13/06 7:48 PM Page 103
Chapter 6: Copying and Correcting Formulas 103

1. Display the floating Formula Auditing toolbar (Tools➪Formula Auditing➪Show
Formula Auditing Toolbar).
2. Remove the range name, source, given to cell A2 in the Formulas worksheet by
selecting source in the Define Name dialog box (Insert➪Name➪Define) and then
clicking the Delete button before you select OK.
As soon as you remove the range name, the spreadsheet on the Formulas work-
sheet fills with #NAME? error values. Now use the Trace Error button to trace the
source of this error value.
3. Position the cell cursor in cell C9 with the SUM function that sums the three cell
ranges A2:A8, B4:B8, C4:C8.
4. Click the Error Checking button (the first one) on the Formula Editing toolbar.
Excel displays the Error Checking dialog box, which briefly explains that this
error value is due to the fact that the formula now contains unrecognized text.
This is not something that you can fix by editing the formula on the Formula bar,
so, in this case, you need to trace the error.
5. Select the Trace Error button in the Error Checking dialog box.
Excel draws a diagonal arrow from cell A4 pointing to cell C9, indicating that
this is the direct source of the error (if you select the Previous button in the
Error Checking dialog box, Excel steps the cell cursor back through each of
the cells with the #NAME? error value in the order in which they spread in this
spreadsheet).
In this situation, you need to identify the cell or cells that feed the cells contain-
ing the #NAME? error values.
6. Click the Trace Precedents button on the Formula Auditing toolbar (the second
from the left).
Excel adds three arrows pointing directly to cell C9, showing the precedents of
its error value, the most telling of which is the one drawn from cell A2. If you
didn’t already know that cell A2 was the culprit causing all these error values
(having removed its range name yourself), this precedents diagram would have
tipped you off immediately.
7. Click the Trace Precedents button on the Formula Auditing toolbar a second
time.
Clicking the Trace Precedents or Trace Dependents button a second time (or
double-clicking it to begin with) displays all the indirect precedents and depend-
ents of the formula in the current cell. In this case, Excel now shows the flow and
direction of all the sum formulas both down the columns and across the rows
that contribute both directly and indirectly to its computed value.
8. Click the Remove All Arrows button on the Formula Auditing toolbar (the one
with the eraser) and then close the Error Checking dialog box.
9. Position the cell cursor in cell A4, the first one with a #NAME? error value, and
then click the Trace Dependents button on the Formula Auditing toolbar (the
one fourth from the left).
The program draws arrows showing the direct dependents of the formula in this
cell, which include the formulas down the column that use its value to compute
their own and the formulas across the row in C4 and D4 and, of course, the for-
mula in cell C9.
10. Click the Trace Dependents button on the Formula Auditing toolbar a second time.