Page 119 - Excel Workbook for Dummies

11_798452 ch06.qxp 3/13/06 7:48 PM Page 102
102 Part II: Using Formulas and Functions
its value throughout the entire spreadsheet, making it very difficult to determine its
source. Fortunately, Excel offers some very effective tools on its Formula Auditing
toolbar that you can use to track down the cell that’s causing your error woes by
tracing the relationships between the formulas in the cells of your worksheet.
By tracing the relationships, you can test formulas to see which cells, called direct
precedents in spreadsheet jargon, directly feed the formulas and which cells, called
dependents (nondeductible, of course), depend upon the results of the formulas.
Excel even offers a way to visually backtrack the potential sources of an error value
in the formula of a particular cell.

To display the precedents and dependents of a formula as well as for tracing the
source of errors, you can either use the tools on the Formula Auditing toolbar
(Tools➪Formula Auditing➪Show Formula Auditing Toolbar) shown in Figure 6-5,
or select the equivalent items on the Tools➪Formula Auditing submenu.

Remove Dependent Arrows Trace Errors
Figure 6-5:
You can use Remove Precendent Arrows Circle Invalid Data
the Formula
Error Checking Show Watch Window
Auditing
toolbar to
track down
the source
of spread-
sheet errors Trace Precendents Evaluate Formula
and elimi-
Trace Dependents Clear Validation Circles
nate it.
Remove All Arrows New Comments

When you click the Trace Precedents and Trace Dependents buttons on the Formula
Auditing toolbar (or choose the comparable commands on the Tools➪Formula Auditing
submenu), Excel shows the relationship between a formula and the cells that directly
and indirectly feed it as well as those cells that directly and indirectly depend upon
its calculation. The program establishes this relationship by drawing arrows from the
precedent cells to the active cell and from the active cell to its dependent cells.

If these cells are on the same worksheet, Excel draws solid red or blue arrows (on a
color monitor) extending from each of the precedent cells to the active cell and from
the active cell to the dependent cells. If the cells are not located locally on the same
worksheet (they may be on another sheet in the same workbook or even on a sheet
in a different workbook), Excel draws a black dotted arrow. This arrow comes from or
goes to an icon picturing a miniature worksheet that sits to one side, with the direc-
tion of the arrowheads indicating whether the cells on the other sheet feed the active
formula or are fed by it.

Try It

Exercise 6-6: Finding the Source of Error Values in a Spreadsheet
Open the Exercis6-6.xls workbook in your Chapter 6 folder inside the My Practice
Spreadsheets folder or on the Excel Workbook CD-ROM. This workbook contains
a copy of the Practice Formulas workbook you created as part of the exercises in
Chapter 5. You can use its Formulas and Ext Ref worksheets to practice tracing and
eliminating errors: