How to fix a circular reference error

Circular reference errors occur when a formula refers back to its own cell. For example, in the example shown, the formula in F7 is:

=F5+F6+F7

To resolve circular references, you'll need to find the cell(s) with incorrect cell references and adjust as needed. The article below provides more information and steps to resolve.

Explanation

A circular reference occurs when a formula refers directly to its own cell, or refers to another cell that depends on the original cell. This creates an infinite loop that cannot be resolved. For example, if cell A1 contains a formula that refers to B1, and B1 contains a formula that refers to A1, this creates a circular reference. Circular reference errors are not like other errors in Excel in that they don't display on the worksheet and don't have a numeric error code. To find circular references, navigate to Formulas > Error checking > Circular references.

Example

In the worksheet shown, the formula in F7 is:

=F5+F6+F7

This creates a circular reference because the formula, entered in cell F7, refers to F7. This in turn throws off other formula results in D7, C11, and D11:

=F7 // formula in C7
=SUM(B7:C7) // formula in D7
=SUM(C5:C9) // formula in C11
=SUM(D5:D9) // formula in D11

Circular references can cause many problems (and a lot of confusion) because they may cause other formulas to return zero, or a different incorrect result.

The circular reference error message

When a circular reference occurs in a spreadsheet, you'll see a warning like this:

"There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing these references, or moving the formulas to different cells."

Circular reference error message dialog

This warning will appear sporadically while editing, or when a worksheet is opened.

Finding and fixing circular references

To resolve circular references, you'll need to find the cell(s) with incorrect cell references and adjust as needed. However, unlike other errors (#N/A, #VALUE!, etc.) circular references don't appear directly in the cell. To find the source of a circular reference error, use the Error Checking menu on the Formulas tab of the ribbon.

Error checking menu on Formulas tab of ribbon