Circular References in Excel Formulas

Circular References in Excel Formulas
Circular References in Excel Formulas. © Ted French

A circular reference occurs in Excel when:

  1. A formula contains a cell reference to the cell containing the formula itself. An example of this type of circular reference is shown in the image above where the formula in cell C1 contains a reference to that cell in the formula: = A1 + A2 + A3 + C1
  2. A formula references another formula which eventually refers back to the cell containing the original formula. An example of this type of indirect reference as it is known is shown in the second example in the image where the blue arrows linking cells A7, B7, and B9 indicate that the formulas in these cells all reference each other.

Circular Reference Warning

As shown in the image above, if a circular reference occurs in an Excel worksheet, the program displays an Alert dialog box indicating the problem.

The message in the dialog box is specifically worded because not all circular references in formulas are unintentional as outlined below.

"Careful, we found one or more circular references in your workbook which might cause your formula to calculate incorrectly"

User Options

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 an unintentional circular reference occurs; 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.

Many unintentional cell references can be corrected without the need for error checking by simply correcting the cell references used in the formula. Rather than typing cell references into a formula, use pointing ------------------ clicking on cell references with the mouse ---------------------- to enter references into a formula.

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:

  1. Click on the File tab (or the Office button in Excel 2007)
  2. Click Options to open the Excel Options dialog box
  3. In the left-hand panel of the dialog box, click on Formulas
  4. In the right-hand panel of the dialog box, select the Enable iterative calculation check box

Below the checkbox 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

Displaying Zeros in the Affected Cells

For cells containing circular references, Excel displays either a zero as shown in cell C1 in the example or the last calculated value in the cell.

In some instances, formulas may run successfully before they try to calculate the value of the cell reference where they are located. When that happens, the cell containing the formula displays the value from the last successful calculation.

More On the Circular Reference Warning

After the first instance of a formula containing a circular reference in a workbook,  Excel won't necessarily display the warning message again. It depends on circumstances of how and where the additional circular references are created.

Examples of when the alert box containing the warning message will be displayed for subsequent circular references include:

  • The first instance of a circular reference in any open workbook;
  • After removing all circular references in all open workbooks, a new circular reference is created;
  • After closing all workbooks, a formula containing a circular reference is created in a new workbook;
  • A workbook containing a circular reference is opened.