Chapter 32: Making Your Worksheets Error-Free
665
Identifying dependents
You can identify formula cells that use a particular cell in a number of ways:
l Display the Go to Special dialog box. Select the Dependents option and then select
either Direct Only (for direct dependents only) or All Levels (for direct and indirect
dependents). Click OK. Excel selects the cells that depend upon the active cell. This tech-
nique is limited to identifying cells on the active sheet only.
l Press Ctrl+]. This selects all direct dependent cells on the active sheet.l (^) Press Ctrl+Shift+}. This selects all dependent cells (direct and indirect) on the active
sheet.
l (^) Choose Formulas ➪ Formula Auditing ➪ Trace Dependents. Excel will draw arrows to
indicate the cell’s dependents. Click this button multiple times to see additional levels of
dependents. Choose Formulas ➪ Formula Auditing ➪ Remove Arrows to hide the arrows.
Tracing error values
If a formula displays an error value, Excel can help you identify the cell that is causing that error
value. An error in one cell is often the result of an error in a precedent cell. Activate a cell that con-
tains an error value and then choose Formulas ➪ Formula Auditing ➪ Error Checking ➪ Trace
Error. Excel draws arrows to indicate the error source.
Fixing circular reference errors
If you accidentally create a circular reference formula, Excel displays a warning message —
Circular Reference — with the cell address, in the status bar, and also draws arrows on the
worksheet to help you identify the problem. If you can’t figure out the source of the problem,
choose Formulas ➪ Formula Auditing ➪ Error Checking ➪ Circular References. This command
displays a list of all cells that are involved in the circular references. Start by selecting the first cell
listed and then work your way down the list until you figure out the problem.
Using background error-checking feature
Some people may find it helpful to take advantage of the Excel automatic error-checking feature.
This feature is enabled or disabled via the Enable Background Error Checking check box, found on
the Formulas tab of the Excel Options dialog box, shown in Figure 32.10. In addition, you can use
the check boxes in the Error Checking Rules section to specify which types of errors to check.
When error checking is turned on, Excel continually evaluates the formulas in your worksheet. If a
potential error is identified, Excel places a small triangle in the upper-left corner of the cell. When
the cell is activated, a Smart Tag appears. Clicking this Smart Tag provides you with options.
Figure 32.11 shows the options that appear when you click the Smart Tag in a cell that contains a
#DIV/0! error. The options vary, depending on the type of error.