Page 173 - Excel Timesaving Techniques for Dummies

33_574272 ch29.qxd 10/1/04 10:49 PM Page 158
29 Eliminating Errors
with Error Tracing

Technique

espite your best efforts, it’s almost impossible to prevent all for-
Save Time By mula errors from cropping up. Possibly the worst thing about this
Dis that the errors often tend to spread far and wide, given the web
Using the Trace
Precedents feature to of formula interdependencies in a spreadsheet. The biggest challenge in
find all cells feeding into such situations is to track down the root of the problem — that is, the
a formula cell with the original formula error that causes the error values to sprout
up all over the place like dandelions over a field after a spring rain.
Using the Trace
Dependents feature to Fortunately, Excel offers some effective tools for tracking down the cell
find all the cells fed by a that’s causing your error woes by tracing the relationships among the
formula formulas in the cells of your worksheet. By tracing the relationships, you
Using the Trace Error fea- can test formulas to see which cells, called direct precedents in spread-
ture to find and fix the sheet jargon, directly feed the formulas and which cells, called depend-
original bad formula ents (nondeductible, of course), depend upon the formulas’ results. Excel
even offers a way to visually backtrack the potential sources of an error
value in the formula of a particular cell.

Formula Auditing 101

The easiest method for tracing the relationship among cells is offered by
the tools on the Formula Auditing toolbar, shown in Figure 29-1. To dis-
play the Formula Auditing toolbar, choose Tools➪Formula Auditing➪
Show Formula Auditing Toolbar or choose Formula Auditing on the
shortcut menu of one of the displayed toolbars. When you first display
the Formula Auditing toolbar, Excel automatically makes it a floating
toolbar, which you can dock as you see fit.
This versatile toolbar contains the following tools (from left to right) that
you can put to good use in your never-ending struggle for truth, justice,
and perfection in your Excel workbooks:

Trace Precedents: When you click this button, Excel draws arrows to
the cells (the so-called direct precedents) that are referred to in the for-
mula inside the selected cell. When you click this button again, Excel
adds tracer arrows that show the cells (the so-called indirect prece-
dents) that are referred to in the formulas in the direct precedents.