Microsoft Office Tutorials and References
In Depth Information
Troubleshoot a Formula by Stepping Through Each Part
Troubleshoot a Formula by
Stepping Through Each Part
If you have a formula that is returning an
inaccurate or erroneous result, you can
troubleshoot the problem by stepping through
each part of the formula.
Simple formulas that add or multiply a few
numbers or use a straightforward function such
as SUM are usually easy to troubleshoot
because they contain only a few operators and
However, many Excel formulas can be quite
complex, with functions nested inside other
functions, multiple sets of parentheses, several
different operators, multiple range references,
and so on. These more involved formulas are
much harder to troubleshoot because it is
often not obvious what part of the formula is
causing the trouble.
You can use the Evaluate Formula command
to help troubleshoot such formulas. This
command enables you to step through the
various parts of the formula to see the
preliminary results returned by each part. By
examining these interim results, you can often
see where your formula goes awry.
If you still have trouble pinpointing the error,
see the section “Audit a Formula to Locate
Errors,” later in this chapter.
1 Select the cell that
contains the formula you
want to troubleshoot.
2 Click the Formulas tab.
3 Click Evaluate Formula.
The Evaluate Formula
dialog box appears.
Excel underlines the first
expression that it will
4 Click Evaluate.