Dealing with Circular References

When you enter formulas, you may occasionally see a message from Excel like the one shown in Figure 2-7. This indicates that the formula you just entered will result in a circular reference.

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

A circular reference occurs when a formula refers to its own value, either directly or indirectly. For example, if you enter =A1 into cell A3, =A3 into cell B3, and =B3 into cell A1, this produces a circular reference because 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. The calculation of these formulas continues in a circle forever, never resolving to any value.

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

Normally, you'll want to correct any circular references, so you should click OK. When you do so, Excel inserts tracing arrows and displays the Help topic for circular references. The status bar will display Circular References: A3, in this case. To resolve the circular reference, choose Formulas Formula Auditing Error Checking Circular References to see a list of cells involved in the circular reference. Click each cell in turn and try to locate 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 until the status bar no longer reads Circular References.

Cross Ref 

In a few situations, you may want to use a circular reference intentionally. Refer to Chapter 16 for some examples.

Tip 

Instead of navigating to each cell using the Circular References submenu, you can click on the tracer arrows to quickly jump between cells.

If you ignore the circular reference message (by clicking Cancel), Excel enables you to enter the formula and displays a message in the status bar reminding 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).

Caution 

Excel doesn't warn you about a circular reference if you have the Enable Iterative Calculation 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 Maximum 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 indicates an error that you must correct.

When the formula in a cell refers to that cell, the cause of the circular reference is quite obvious and is, therefore, easy to identify and correct. For this type of circular reference, Excel does not show tracer arrows. For indirect circular reference, like in the preceding example, the tracer arrows can help you identify the problem.