Microsoft Office Tutorials and References
In Depth Information
Handling circular references
reference. A circular reference occurs when a formula refers to its own value — either
directly or indirectly. For example, you create a circular reference if you enter =A1+A2+A3
into cell A3 because the formula in cell A3 refers to cell A3. Every time the formula in A3 is
calculated, it must be calculated again because A3 has changed. The calculation could go
If you see this warning, you know that the formula you entered will result
in a circular reference.
When you get the circular reference message after entering a formula, Excel gives you two
Click OK, and Excel displays a Help screen that tells you more about circular
Click Cancel to enter the formula as is.
Regardless of which option you choose, Excel displays a message in the left side of the
status bar to remind you that a circular reference exists.
Excel won’t tell you about a circular reference if the Enable iterative calculation setting is in effect. You can check
this setting in the Formulas tab of the Excel Options dialog box, under Calculation options. If Enable iterative
calculation is turned on, Excel performs the circular calculation exactly the number of times specii ed in the Maximum
Iterations text box (or until the value changes by less than 0.001 or whatever value is in the Maximum Change text
box). In a few situations, you may use a circular reference intentionally. In these cases, the Enable iterative
calculation setting must be on. However, it’s best to keep this setting turned off so that you’re warned of circular references.
Usually, a circular reference indicates an error that you must correct.
Often, a circular reference is quite obvious and easy to identify and correct. But when a
circular reference is indirect (as when a formula refers to another formula that refers to yet
another formula that refers back to the original formula), it may require a bit of detective
work to get to the problem.