Microsoft Office Tutorials and References

In Depth Information

**Finding and correcting errors in calculations**

Another technique you can use to find the source of formula errors is to ensure that the

appropriate cells are providing values for the formula. For example, you might want to

calculate the total number of deliveries for a service level, but you could accidentally create a

formula referring to the service levels’ names instead of their package quantities. You can

identify the source of an error by having Excel trace a cell’s
precedents
, which are the cells

that have values used in the active cell’s formula. To do so, click the Formulas tab, and then

in the Formula Auditing group, click Trace Precedents. When you do, Excel identifies those

cells by drawing a blue tracer arrow from the precedents to the active cell.

You can also audit your worksheet by identifying cells that contain formulas that use a value

from a given cell. For example, you might use one region’s daily package total in a formula

that calculates the average number of packages delivered for all regions on a given day.

Cells that use another cell’s value in their calculations are known as
dependents
, meaning

that they depend on the value in the other cell to derive their own value. As with tracing

precedents, you can click the Formulas tab, and then in the Formula Auditing group, click

Trace Dependents to have Excel draw blue arrows from the active cell to those cells that

have calculations based on that value.

If the cells identified by the tracer arrows aren’t the correct cells, you can hide the arrows

and correct the formula. To hide the tracer arrows on a worksheet, display the Formulas tab,

and then in the Formula Auditing group, click Remove Arrows.

If you prefer to have the elements of a formula error presented as text in a dialog box, you

can use the Error Checking dialog box to view the error and the formula in the cell in which

the error occurs. To open the Error Checking dialog box, display the Formulas tab, and then

in the Formula Auditing group, click the Error Checking button. You can use the controls in

the Error Checking dialog box to move through the formula one step at a time, to choose

to ignore the error, or to move to the next or the previous error. If you click the Options

button in the dialog box, you can also use the controls in the Excel Options dialog box to

change how Excel determines what is an error and what isn’t.