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

operands.

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.

2

3

1

The Evaluate Formula

dialog box appears.

Excel underlines the first

expression that it will

evaluate.

4
Click Evaluate.

●

4