Microsoft Office Tutorials and References

In Depth Information

the formulas create a circle where each formula depends on the one before it. Every time the formula in A3 is

calculated, it affects the formula in B3, which in turn affects the formula in A1. The result of the formula in A1

then causes A3 to recalculate, and the calculation circle starts all over again.

Figure 2-8:
Excel's way of telling you that your formula contains a circular reference.

When you enter a formula that contains a circular reference, Excel displays a dialog box with two options: OK

and Help.

Normally, you'll want to correct any circular references, so you should click OK. After you do so, Excel inserts

tracing arrows and displays the Help topic for circular references. The status bar displays Circular References:

A3
,
in this case. To resolve the circular reference, choose Formulas
⇒
Formula Auditing
⇒
Error Checking
⇒
Cir-

cular References to see a list of the cells involved in the circular reference. Click each cell in turn and try to loc-

ate the error. If you cannot determine whether the cell is the cause of the circular reference, navigate to the next

cell on the Circular References submenu. Continue reviewing each cell on the Circular References submenu un-

til the status bar no longer reads Circular References
.

Instead of navigating to each cell using the Circular References submenu, you can click

the tracer arrows to quickly jump between cells.

After you enter a formula that contains a circular reference, Excel displays a message in the status bar remind-

ing you that a circular reference exists. In this case, the message reads Circular References: A3
.
If you activate a

different worksheet or workbook, the message simply displays Circular References (without the cell reference).

Excel doesn't warn you about a circular reference if you have the Enable Iterative Cal-

culation setting turned on. You can check this in the Excel Options dialog box (in the

Calculation section of the Formulas tab). If this option is checked, Excel performs the

circular calculation the number of times specified in the Maximum Iterations field (or

until the value changes by less than .001 — or whatever other value appears in the Max-

imum Change field). You should, however, keep the Enable Iterative Calculation setting

off so that you'll be warned of circular references. Generally, a circular reference indic-

ates an error that you must correct.