Avoiding Circular References in Excel 2007 Formulas

A circular reference occurs when a cell in an Excel 2007 worksheet refers to itself, whether directly or indirectly. For example, if =100+A2 is entered into cell A2, then a direct circular reference has been created. An indirect circular reference is when the formula in a given cell refers to one or more other cells that in return refer back to the original cell. For example a formula in A1 refers to cell A2, A2 refers to A3, and A3 refers back to A1.

When Excel encounters a circular reference in a worksheet, a Circular Reference Warning appears in a dialog box. You have two options in this dialog box:

* Click OK to access the Help system Circular Reference topic.

* Click Cancel to accept the formula as is.

If Automatic Calculation is turned off, then a circular reference is unnoticed until a manual recalculation is done (by pressing F9) or the setting is changed to Automatic Calculation. You can change these settings via the Calculation Options button on the Formulas tab.

Additional calculation options can be set in the Formulas section of the Excel Options dialog box (click the Office button and select Excel Options). Note that the Enable Iterative Calculation check box is here as well. When this is selected, circular references are allowed. How they calculate values in this case is dependent on the Maximum Iterations and Maximum Change settings.
Setting calculation and iteration settings in the Excel Options dialog box.
Setting calculation and iteration settings in the Excel Options dialog box.

Excel 2007 offers another approach to hunting down circular references. Select the arrow beside the Error Checking button in the Formula Auditing group on the Formulas tab and point to the Circular References option. The resulting menu displays the location of all circular references in the active worksheet. Clicking on one of the listed cells will take you to the cell with the circular reference. This allows you to get to them easily instead of having to review all your formulas.