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:
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:
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."
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.
Select the Circular References item to see the source of circular references:
Below, the circular reference has been fixed and other formulas now return correct results: