Page 177 - Excel Timesaving Techniques for Dummies

33_574272 ch29.qxd 10/1/04 10:50 PM Page 162
162
Technique 29: Eliminating Errors with Error Tracing
button on the Formula Auditing toolbar to engage
the use of Excel’s faithful old Trace Error feature.

• Figure 29-6: Clicking the Trace Dependents button to
display all the cells that use the formula’s
result.
• Figure 29-7: Finding the source of a #DIV/0! error with
the Trace Error button.
As this figure shows, Excel first draws tracer arrows
from cell B9 to cells C12 and C13, indicating that C12
and C13 are the direct dependents of cell B9. Then it Figure 29-7 shows the result of clicking the Trace
Error button (unfortunately without color, so you
draws tracer arrows from cells C12 and C13 to E12
and E13, respectively, the direct dependents of C12 can’t tell which trace arrows were drawn in blue or
red). Note that Excel has selected cell C12, although
and C13 and the indirect dependents of B9. Finally, it
draws a tracer arrow from cell E12 to another sheet cell E13 was the current one when I clicked the
Trace Error button. To cell C12, Excel has drawn a
in the workbook (indicated by the dotted tracer
arrow pointing to the worksheet icon). blue tracer arrow (you’ll have to take my word for it)
that identify cell B9 as its direct precedent. From cell
C12, the program has drawn a single red tracer
Finding the Original Error arrow (again, you have to trust me on this) from cell
C12 to cell E12 that identifies its direct dependent.
and Fixing Its Formula
After the Error Trace feature has located the prob-
lem formula, you can click the Error Checking button
You use the Trace Error button on the Auditing on the Formula Auditing toolbar to fix it. When you
Toolbar when you need to track the source of a for-
click this button, an Error Checking dialog box simi-
mula error so that you can correct it. When you click lar to the one shown in Figure 29-8 appears. This dia-
this button when the cell pointer is in a cell that con-
log box not only diagnoses the source of the error
tains an error value, Excel attempts to track down value but also offers you several choices on how to
the source by selecting the cell with the original
proceed:
offending formula and then drawing blue tracer
arrows to its direct precedents and red tracer
Help on This Error: Click this button to display
arrows to all its direct dependents.
a Microsoft Excel Help window with information
on the error.
Figure 29-7 shows the sample worksheet after I made
a damaging modification that left three cells — C12, Show Calculation Steps: Click this button to
E12, and E13 — with #DIV/0! errors. To find the open an Evaluate Formula dialog box that
origin of these error values and identify its cause, enables you to step through the formula to pin-
I selected cell E13 and then clicked the Trace Error point exactly where the computation goes
wrong.