Exceljet

Quick, clean, and to the point

How to fix a circular reference error

Excel formula: How to fix a circular reference error
Explanation 

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

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

Select the Circular References item to see the source of circular references:

Show circular references in error checking menu

Below, the circular reference has been fixed and other formulas now return correct results:

Circular reference fixed, formulas show correct results again

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.