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

options:

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

Circular

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.

➜

Formula Auditing

➜

Error Checking

➜

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

chapter.