Chapter 19: Making Your Formulas Error-Free
19
■ (^) 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 contains 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. It 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 the 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 19.1. 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 work-
sheet. 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 drop-down control appears. Clicking this drop-
down control provides you with options. The options vary, depending on the type of error.
In many cases, you’ll choose to ignore an error by selecting the Ignore Error option.
Selecting this option eliminates the cell from subsequent error checks. However, all previ-
ously ignored errors can be reset so that they appear again. (Use the Reset Ignored Errors
button on the Formulas tab of the Excel Options dialog box.)
You can choose Formulas ➪ Formula Auditing ➪ Error Checking to display a dialog box that
describes each potential error cell in sequence, much like using a spell-checking command.
The error-checking feature isn’t perfect. In fact, it’s not even close to perfect. In other words, you can’t assume that
you have an error-free worksheet simply because Excel doesn’t identify potential errors! Also, be aware that this
error-checking feature won’t catch a common type of error, namely, overwriting a formula cell with a value.