A circular reference occurs in Excel when:
- A formula contains a cell reference to the cell containing the formula itself. An example of this type of circular reference would be if the formula =B4 + B5 was located in cell B5.
- As shown in the image to the right, a formula references another formula which either directly or indirectly refers back to the cell containing the original formula.
Circular Reference Warning
If a circular reference occurs, Excel displays an Alert dialog box indicating the problem.
User options when this dialog box appears are to click OK or Help - neither of which will fix the circular reference problem.
If you read the long and somewhat confusing message in the dialog box you will discover that:
- clicking on Help is meant to be used when unintentional circular references occur - this takes you to Excel's help file information on circular references
- clicking OK is used to tell Excel that the circular reference was done intentionally and that it should leave it in place
Unintentional Circular References
If the circular reference was done unintentionally, the help file information will tell you how to go about finding and removing circular references.
The help file will direct you to use Excel's Error Checking tool located under Formulas > Formula Auditing on the ribbon.
Intentional Circular References
Excel's circular reference doesn't offer a fix for a circular reference problem because not all circular references are mistakes.
While these intentional circular references are less common than the unintentional ones, they can be used if you want Excel to iterate or run a formula multiple times before producing a result.
Enabling Iterative Calculations
Excel has an option to enable these iterative calculations if you plan to use them.
To enable iterative calculations:
- Click on the File tab (or the Office button in Excel 2007)
- Click Options to open the Excel Options dialog box
- In the left hand panel of the dialog box, click on Formulas
- In the right hand panel of the dialog box, select the Enable iterative calculation check box
Below the check box options are available for:
- setting the maximum number of iterations - the number of times Excel should recalculate the formula
- setting the maximum amount of change acceptable between calculation results - the smaller the number, the more accurate the result