Microsoft Office Tutorials and References
In Depth Information
Chapter 16: Intentional Circular References
Figure 16-1: Excel’s way of telling you that your formula contains a circular reference.
Every time the formula in A10 is calculated, it must be recalculated because A10 has changed. In
theory, the calculation could continue forever while the value in cell A10 tries to reach infinity.
Correcting an accidental circular reference
When you see the circular reference message after entering a formula, Excel gives you two
h Click OK to attempt to locate the circular reference. This also has the annoying side effect
of displaying a Help screen whether you need it or not.
h Click Cancel to enter the formula as is.
Most circular reference errors are caused by simple typographical errors or incorrect range
specifications. For example, when creating a SUM formula in cell B10, you might accidentally specify
an argument of B1:B10 instead of B1:B9.
If you know the source of the problem, click Cancel. Excel displays a message in the status bar to
remind you that a circular reference exists. In this case, the message reads Circular
References: B10 . If you activate a different workbook or worksheet, the message simply
displays Circular References (without the cell reference). At this point, you can then edit the
formula and fix the problem.
If you get the circular message error but you don’t know what formula caused the problem, you
can click OK in response to the dialog box alert. When you do so, Excel shows the Help topic on
circular references and also draws errors on the worksheet, which may help you identify the
problem. For more help, choose Formulas
References to see a list of cells involved in the circular reference (see Figure 16-2). Click the first
cell in the list to move to that cell, and examine its formula. If you cannot determine whether that
cell caused the circular reference, move to the next cell by selecting it from the list. Continue to
review the formulas until the status bar no longer displays Circular References.
The Circular References command on the Ribbon is not available if you have the Enable
Iterative Calculation setting turned on. You can check this setting in the Excel Options
dialog box (in the Formulas section). I discuss more about this setting later in this