Page 174 - Excel Timesaving Techniques for Dummies

33_574272 ch29.qxd 10/1/04 10:49 PM Page 159
159
Formula Auditing 101
Error Checking New Comment: Clicking this button opens a
comment box attached to the current cell where
Remove Precedent Arrows you can add a text note.
Remove Dependent Arrows Circle Invalid Data: Clicking this button draws
red circles around all the data entries in the
Circle Invalid Data
worksheet that don’t currently contain valid data
Trace Error Show Watch Window (as defined with the data validation feature —
see Technique 14 for information on using data
validation to restrict input in a cell).
Clear Validation Circles: Clicking this button
removes all circles drawn by clicking the Circle
Trace Dependents Evaluate Formula Invalid Data button. (To remove individual cir-
cles, select the cell and then enter the data that’s
Remove All Arrows Clear Validation Circles required by the data validation assigned to the
cell.)
New Comment
• Figure 29-1: The Formula Auditing toolbar gives you Show Watch Window: Clicking this button opens
great tools for hunting down and eliminating the Watch Window dialog box, which displays
errors. the workbook, sheet, cell location, range name,
current value, and formula in any cells that you
Remove Precedent Arrows: Clicking this button add to the watch list. To add a cell to the watch
gets rid of the arrows that were drawn when you list, click the cell in the worksheet, click the Add
clicked the Trace Precedents button. Watch button in the Watch Window dialog box,
and then click Add in the Add Watch dialog box
Trace Dependents: When you click this button, that appears.
Excel draws arrows from the selected cell to the
cells (the so-called direct dependents) that use, Evaluate Formula: Clicking this button opens
or depend on, the results of the formula in the the Evaluate Formula dialog box, where you can
selected cell. When you click this button again, have Excel evaluate each part of the formula in
Excel adds tracer arrows identifying the cells the current cell. This can be quite useful in for-
(the so-called indirect dependents) that refer to mulas that nest many functions within them.
formulas found in the direct dependents.
Clicking the Trace Precedents and the Trace
Remove Dependent Arrows: Clicking this button Dependents buttons on the Formula Auditing toolbar
gets rid of the arrows that were drawn when you (or choosing both Trace Precedents and Trace
clicked the Trace Dependents button.
Dependents on the Tools➪Formula Auditing cascad-
Remove All Arrows: Click this button to remove ing menus) lets you see the relationship between a
all the arrows drawn, no matter what button or formula and the cells that directly and indirectly
pull-down command you used to put them there. feed it, as well as those cells that directly and indi-
rectly depend upon its calculation. Excel establishes
Trace Error: When you click this button, Excel this relationship by drawing arrows from the prece-
attempts to locate the cell that contains the orig-
inal formula that has an error. If Excel can find dent cells to the active cell and from the active cell
this cell, it selects it and then draws arrows to to its dependent cells.
the cells feeding it (the direct precedents) and If these cells are on the same worksheet, Excel
the cells infected with its error value (the direct draws solid red or blue arrows (on a color monitor)
dependents). Note that you can use this button
only on a cell that contains an error value. extending from the precedent cells to the active cell