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

on forever.

FIGURE 15.14

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

options:

Click OK, and Excel displays a Help screen that tells you more about circular

references.

■

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.