Microsoft Office Tutorials and References
In Depth Information
Working with circular references
Figure 12-31 The discount formula in cell N30 is circular because it depends on the total, which
in turn depends on the discount value in N30.
When Excel detects a circular reference, tracer arrows appear on the worksheet. To draw
additional arrows to track down the source of an unintentional circular reference, select the
offending cell and then click Trace Precedents on the Formulas tab to draw tracer arrows to
the next level of precedent cells, as shown in Figure 12-31.
For more information about tracer arrows and other auditing features, see “Auditing and
documenting worksheets” in Chapter 8.
You’ll find the Circular Reference.xlsx file with the other examples on the companion
After you dismiss the error message shown earlier in Figure 12-29, the formula will not
resolve until you allow Excel to recalculate in controlled steps. To do so, click the File tab,
Options, Formulas category, and in the Calculation Options section, select the Enable
Iterative Calculation check box. Excel recalculates all the cells in any open worksheets that
contain a circular reference.
If necessary, the recalculation repeats the number of times specified in the Maximum
Iterations box (100 is the default). Each time Excel recalculates the formulas, the results in
the cells get closer to the correct values. If necessary, Excel continues until the difference
between iterations is less than the number typed in the Maximum Change text box (0.001
is the default). Thus, using the default settings, Excel recalculates either a maximum of 100
times or until the values change less than 0.001 between iterations, whichever comes first.
If the word Calculate appears in the status bar after the iterations are finished, more
iterations are possible. You can accept the current result, increase the number of iterations, or
lower the Maximum Change threshold. Excel does not repeat the “Cannot Resolve Circular
Reference” error message if it fails to resolve the reference. You must determine when the