Tracing errors in formulas
Tracing errors in formulas lets you locate the source of calculation errors. If you are looking at a formula cell, you can see all cells that provide data to that formula. It can also show all formulas that use the data from a particular cell.
You can locate a circular cell reference, which is a formula that relies upon its own result to calculate the formula. For example, placing the formula +A1+A2 in cell A1 produces a circular cell reference.
Tracing cell precedents lets you view all cells that provide data to or influence a formula cell. All first-level precedent cells are outlined in blue. A first-level precedent cell provides data directly to the formula being traced. Once you have identified the first-level precedent cells, you can find the cells that influence them and repeat the process until you find the cell causing the error.
You can also trace cell dependents. When you trace dependents of a cell, you see all formulas that use the data from the selected cell.
Finally, you can trace formula cell references using the Formula Composer dialog box.
For additional information about tracing errors see “Guidelines for solving errors in formulas.”
To locate a circular cell reference |  |
|
1.
|
|
Click Format
 Notebook properties. |
|
2.
|
|
Click the Recalc settings tab.
|
|
The circular cell reference displays to the right of the # of iterations box.
|
|
•
|
|
If the Circular cell reference indicator
 button displays on the application bar, the notebook contains a circular cell reference. |
|
•
|
|
Elaborate formulas are sometimes deliberately constructed to contain circular cell references, because the formulas require multiple evaluations to attain an acceptable degree of accuracy.
|
To trace cell precedents | |
|
1.
|
|
Select a cell containing a formula.
|
|
2.
|
|
Click Tools
Auditing Show auditing toolbar. |
|
3.
|
|
Click the Trace precedents
 button to display the first level of precedents. |
|
4.
|
|
Continue to click the button until no more precedents display.
|
To trace cell dependents | |
|
1.
|
|
Select a cell.
|
|
2.
|
|
Click Tools
Auditing Show auditing toolbar. |
|
3.
|
|
Click the Trace dependents
 button. |
|
4.
|
|
C
ontinue to click the Trace dependents button until no more dependents display. |
To trace formula cell references | |
|
1.
|
|
Select a cell containing a formula.
|
|
2.
|
|
Click the Formula Composer
 button. |
|
3.
|
|
Click the cell reference in the outline pane.
|
|
4.
|
|
Click the Follow formula
 button on the Formula Composer toolbar. |
|
•
|
|
Click the Back
 button on the Formula Composer toolbar to return to the standard display. |