Microsoft Office Tutorials and References
In Depth Information
Solving Formula Errors
Excel’s solution is to provide a feature—called the Evaluate Formula tool—that
evaluates your formula one sub-expression at a time. Using this tool, you can watch as
Excel computes your formula, up until the point where the error appears.
To watch the step-by-step execution of a formula that contains an error, follow these
You don’t need to highlight the formula, you just need to be in the offending
2. ChooseFormulas ➝ FormulaAuditing ➝ EvaluateFormula.
The Evaluate Formula dialog box appears (Figure 18-1), with the formula in a
large, multiline textbox.
The first two arguments in this formula
have been evaluated. (The second
argument  is italicized, indicating
that Excel calculated it in the last step.)
The next time you click Evaluate, Excel
evaluates the third argument, which
is underlined. If you want to show the
contents of this cell before evaluating
it, you can click Step In.
Excel evaluates the sub-expression and replaces it with the calculated value. It
might replace a cell reference with the cell’s actual value, evaluate an arithmetic
operator, or execute a function. The value appears in italics, indicating that it’s
the most recent value that Excel has calculated.
When the error occurs, you’ll see the error code appear in your formula. When
you click Evaluate again, the error code spreads, encompassing the whole
expression or the function that uses it. Consider the ill-fated formula = 5/0 . The +
first step (the division) creates a divide-by-zero error, and the formula appears
as = #DIV/0! . But you can’t add 1 to an error, so, in the next step, the error +
spreads to the whole formula, which becomes =#DIV/0! in the end.