Microsoft Office Tutorials and References

In Depth Information

**Tracing Precedents and Dependents**

Solving Formula

Errors

Tip:
Step-by-step evaluation isn’t just for solving errors. It can also help you understand
why
a formula

doesn’t produce the result you expect. You can use the Evaluate Formula dialog box with a formula that

doesn’t cause an error in exactly the same way as one that does. By watching the calculation proceed

stepby-step, you may realize that the order of operations Excel follows is subtly different from the order you

expected. You can then edit the formula accordingly.

Tracing Precedents and Dependents

The Evaluate Formula dialog box is one way you can examine complex formulas’

anatomy. However, depending on the complexity of your formulas, you can end up

having to move through a long series of steps before you find the problem. In this

case, you may be interested in using a different approach, one that uses Excel’s ability

to graphically trace linked cells. This feature isn’t any better or worse than the

Evaluate Formula dialog box—it’s just another tool that you can use to resolve problems,

depending on the situation and your own preference.

First, here’s a quick review of how Excel thinks about precedents and dependents.

Consider the following formula:

=A1+B1

If this formula is in cell C1, that makes A1 and B1
precedents
of C1. In other words,

C1 relies on the values in A1 and B1 in order to do its work. If either of these cells

contains an error value, the problem spreads into C1. You can say that C1 is the

dependent
of both A1 and B1.

Excel’s tracing features let you see a graphical representation of these relationships—

in the form of blue arrows—right on your worksheet without needing to look in

another window or dialog box.

To see tracing in action, move to a cell that contains one or more cell references, and

then choose Formulas
➝
Formula Auditing
➝
Trace Precedents. Excel displays solid

blue arrows that link the cells together. If you click Trace Precedents in the cell C1

that contains that formula =
, you see two arrows. One points from A1 to C1,
B1

and the other points from B1 to C1. Figures 18-3 and 18-4 show examples.

Note:
If a formula references a cell in another worksheet or workbook, Excel draws a dotted line linking

your cell to a small grid icon. This icon represents the other worksheet or workbook and can’t see the

actual cell that the formula links to.