Circular reference in Excel

  • Post author:
  • Post category:Uncategorized

When working with Excel, we sometimes encounter this warning message:

“Careful, we found one or more circular references in your workbook that might cause your formulas to calculate incorrectly.”

Figure 1.  Circular Reference warning message

As defined, a circular reference means that a cell contains a formula that refers to itself, or it contains a reference to another cell, whose value also refers back to the selected cell.  

Circular reference examples

Formula containing the cell itself

Below is an example of a formula that results to a circular reference.  Note that the formula in cell C8 is =SUM(C3:C8), which contains the cell C8 itself.  

Figure 2.  Formula containing the cell itself

When the warning message displays, we can either click OK, Help or close the message window.  Excel then returns the last calculated result for the cell, or it simply returns zero “0”.

Figure 3.  Zero value for cell with circular reference

Cell reference to a cell that refers back to itself

The example below shows that cell B2 refers to the value in cell C2.  However, C2 also refers back to B2. Hence, the circular reference.

There are cases when precedent or dependent tracer arrows are instantly shown.  We can use this feature to easily trace and fix circular reference.  

Figure 4.  Two cells referring back to each other

How to find circular references?

Error Checking menu

In order to find the cells containing circular references in our worksheet, we follow these steps:

  • Click Formulas tab > Error Checking > Circular References

This option displays the last cell that was entered with a circular reference.  In this case, cell C8.  

Figure 5.  Circular Reference cell address

Status Bar

Alternatively, the status bar also helps us find circular reference, as shown below.  

Figure 6.  Status Bar displays cell with circular reference

If the circular reference exists in another worksheet, the status bar only displays “Circular References”.  

Figure 7.  Status Bar preview of circular reference in another sheet

In order to identify the cell with circular reference in another sheet, we click on the Error Checking menu in Formulas tab.  

Figure 8.  Circular Reference cell address in another sheet

How to remove circular reference?

Excel does not have a formula to remove circular references at once.  That task falls on us and we need to review and edit the formula in each cell identified with circular reference.  For example, in cell C8 below, we can edit the formula such that it does not refer back to itself.

Figure 9.  Circular Reference example

Change the formula in C8 to =SUM(C3:C7).  The cell will display the result of calculations “2700”.  In order to verify that we have removed the circular reference, the Error Checking menu in Formulas tab will not display any cells with circular reference.  

Figure 10.  Output: Remove circular reference

How to allow circular reference?

Generally, we don’t want to have circular references in our worksheet.  However, there are some rare cases where circular references are needed to arrive at a desired result.  

A circular reference is not allowed when iterative calculations are turned off.  By default, iterative calculations are disabled in Excel. In order to enable iterative calculation and thus allow circular reference, we follow these steps:

  • Click File Options
  • In Excel Options dialog box, click Formulas and tick Enable iterative calculation

Figure 11.  Enable iterative calculation in Excel Options