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.