Circular References in Excel Formulas

Roundabout traffic circle road warning sign on a electricity pole
Simon McGill / Getty Images

A circular reference occurs in Excel when a formula contains a cell reference to the cell that contains the formula.

The information in the article applies to Excel versions 2019, 2016, 2013, 2010, Excel Online, and Excel for Mac.

An Example of a Circular Reference

An example of this type of circular reference is shown in the image below where the formula in cell C1 contains a reference to cell C1 in the formula:

= A1 + A2 + A3 + C1

A reference error can occur when a formula references another formula which eventually refers back to the cell containing the original formula.

Screenshot of Excel showing a circular reference

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.

User Options

User options when this dialog box appears are to select OK or Help, neither of which will fix the circular reference problem. When you read the message in the dialog box you'll discover that:

  • Help is meant to be used when an unintentional circular reference occurs. This takes you to Excel's help file information on circular references.
  • 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 shows you how to find and remove circular references. The help file directs you to use Excel's Error Checking tool located on the Formulas tab. Many unintentional cell references can be corrected without the need for error checking by simply correcting the cell references used in the formula.

Screenshot of Excel showing error checking

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. Select File (or the Office button in Excel 2007).

  2. Select Options to open the Excel Options dialog box.

  3. \In the left-hand panel of the dialog box, select Formulas.

  4. In the right-hand panel of the dialog box, select the Enable iterative calculation check box.

Below the checkbox, options are available to:

  • Set the maximum number of iterations; this is the number of times Excel should recalculate the formula.
  • Set 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 0, 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.

Screenshot of Excel showing Error Checking preferences

More On the Circular Reference Warning

After the first instance of a formula containing a circular reference in a workbook, Excel may not 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 displays 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.