Microsoft Office Tutorials and References
In Depth Information
Solving Formula Errors
You can also click Close to stop evaluating the formula and return to your
worksheet at any time.
Gem in the roUGh
Digging Deeper into Linked Formulas
Even when you’ve discovered the sub-expression that’s
causing the trouble, you still may not have found the root
of the problem. If the sub-expression that’s causing the
error is a reference, it may point to another cell that contains
another formula. If it does, then you need to evaluate that
formula step-by-step in order to find the real mistake.
box shows the contents of the referenced cell. Excel also
informs you if the cell contains a formula or a constant (just
read the label at the bottom of the Evaluate Formula dialog
box). If the cell contains a constant or there’s no
calculation left to perform, you need to click Step Out to return to
the original formula. If the cell does contain a formula, you
can click the Evaluate button to start evaluating it—one
subexpression at a time—and then click Step Out once you’re
To evaluate the second formula, you can move to the
appropriate cell and start the step-by-step evaluation process
by clicking the Evaluate Formula button. However, Excel
also provides a useful shortcut that lets you jump from one
formula into another. The secret is the Step In and Step Out
buttons in the Evaluate Formula dialog box (Figure 18-2).
In fact, Excel lets you dig even deeper into chains of linked
formulas. Every time you find a cell reference that points to
another formula-holding cell, you can click Step In to show
the formula in a new text box. You can continue this
process with no practical limit. If you exceed the space
available in the Evaluate Formula dialog box, Excel just adds a
scroll bar to help you out.
When you’re using the Evaluate Formula dialog box, the
Step In button becomes available just before you evaluate
a sub-expression containing a cell reference. If you click
the Step In button at this point, Excel adds a new text box
to the dialog box underneath the first one. This new text
In this example, the Step In button
has taken you three levels deep into
a formula. The formula it’s
evaluating is in the first box; it’s A3+A4+A5.
However, clicking Step In adds a
second box, which reveals that cell
A3 itself contains a formula (B3+C3).
Finally, another click of Step In shows
a third box, which zooms in on the first
part of the second formula (B3), and
shows that the cell it points to holds
the number 84.