A circular reference is a formula that depends on its own value.

The most obvious type is a formula that contains a reference to the same cell in which it’s entered. For example, if you type =C1-A1 in cell A1, Excel displays an error message. This error message appears when you attempt to enter a formula that contains a circular reference.

If a circular reference warning surprises you, this usually means that you made an error in a formula. Click OK, and look at the formula. If the error isn’t obvious, check the cells that the formula refers to.

Choose View, Toolbars, Circular Reference to help track down the problem using the built-in auditing features of Excel. Click the Trace Precedents or Trace Dependents button to draw tracer arrows on the worksheet. These arrows show you a visual path to all the cells involved.

If you click the Trace Precedents button, Excel draws a line from the cell that contains the formula to any cells used by the formula. Each time you click, tracer lines appear for the next level, if any of the cells used by the formula contain formulas themselves.

Many circular references can be resolved. Some circular formulas are useful or even essential, such as the example in file Sales Invoice.xls. These formulas are circular because the formula in cell M29 depends on the value in M30, and the formula in M30 depends on the value in M29.

After you dismiss the error message, the formula will not resolve until you allow Excel to recalculate in controlled steps. To do so, choose Tools, Options, click the Calculation tab, and then select the Iteration option.

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 entered in the Maximum Change 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 answer is close enough.

Excel can perform iterations in seconds, but in complex circular situations, you might want to set the Calculation option to Manual; otherwise, Excel recalculates the circular references every time you make a cell entry.

The useful circular reference scenario described in this section is called convergence: The difference between results becomes smaller with each iterative calculation. In the opposite process, called divergence, the difference between results becomes larger with each calculation. Excel continues iterations until it completes the number you specify.