Microsoft Office Tutorials and References

In Depth Information

**Audit a Formula to Locate Errors**

Audit a Formula

to Locate Errors

If you know or suspect that a formula error is

caused by an error in another cell, you can

audit the formula to locate the cell that is

causing the error.

If a formula error is the result of referencing

other cells that contain errors or inappropriate

values, you must determine which cell is

causing the error. This is straightforward if the

formula references only a single cell, but it

becomes progressively more difficult as the

number of references increases.

To determine which cell is causing the error in

your formula, you can use the auditing features

in Excel to visualize and trace a formula’s input

values and error sources. Auditing operates by

creating
tracers
— arrows that literally point

out the cells involved in a formula. You can

use tracers to find three kinds of cells:

precedents,
which are cells that are directly or

indirectly referenced in a formula;
dependents,

which are cells that are directly or indirectly

referenced by a formula in another cell; and

errors,
which are cells that contain an error

value and are directly or indirectly referenced

in a formula.

Trace Precedents

1
Click the cell containing

the formula whose

precedents you want to

trace.

2
Click the Formulas tab.

3
Click Trace Precedents.

●
Excel adds a tracer arrow

to each direct precedent.

4
Repeat step 3 until you

have added tracer arrows

for all the formula’s

indirect precedents.

2

3

1

2

3

Trace Dependents

1
Click the cell containing

the formula whose

dependents you want to

trace.

2
Click the Formulas tab.

3
Click Trace Dependents.

●

1

Excel adds a tracer arrow

to each direct dependent.

4
Repeat step 3 until you

have added tracer arrows

for all the formula’s

indirect dependents.