Fixes / Excel · Checked September 30, 2026

Excel "There are one or more circular references": how to fix

Short answer

The warning means a formula uses its own cell as an input, either directly (such as =SUM(A1:A10) typed in A10) or through a chain of other cells that loops back to it. To find the cell, go to Formulas > Error Checking > Circular References and click the cell address listed there. Then change the formula so its range leaves out its own cell.

What this message means

Excel calculates a formula by first working out every cell the formula depends on. If one of those cells is the formula's own cell, or depends on it through other cells, Excel is stuck in a loop and cannot reach a final answer. It shows this warning and does not calculate the loop. The affected cells usually show 0 or the last value they had. Excel does not put an error code such as #VALUE! in the cell, so wrong totals can go unnoticed. The warning appears the first time a circular reference is created in a session, and again whenever you open a workbook that already contains one. After that, the status bar at the bottom of the window shows "Circular References" followed by a cell address.

Common causes

Cause	How to tell
A total formula whose range includes its own cell, for example =SUM(B2:B10) entered in B10.	The status bar shows "Circular References: B10" (or similar), and that cell holds a SUM, AVERAGE or similar formula whose range ends on or passes through its own row or column.
A formula that refers to a whole column or row containing itself, such as =SUM(C:C) placed in column C.	The formula uses a full-column reference like C:C or 5:5, and the formula sits inside that same column or row.
An indirect loop across several cells or sheets, where A1 uses B1, B1 uses C1, and C1 uses A1 (possibly on another worksheet).	No single formula points to itself. Formulas > Error Checking > Circular References lists more than one cell, and Trace Precedents arrows lead back to where you started.
The loop is on a different or hidden worksheet from the one you are looking at.	The status bar shows only "Circular References" with no cell address, or the warning appears as soon as the file opens and you cannot see any bad formula on the current sheet.
A model that needs a loop on purpose, such as interest that depends on a balance that includes that interest.	The workbook came from a finance or engineering template and the loop is part of the design. The file's author may have expected iterative calculation to be turned on.

How to fix it

1. Find the circular cell with Error Checking

Excel for Microsoft 365, 2024, 2021, 2019 and 2016 on Windows and Mac. Excel for the web has limited formula auditing tools.

Click OK to close the warning.
Go to the Formulas tab, click the arrow next to Error Checking, and point to Circular References.
Click the first cell address in the list. Excel selects that cell, even when it is on another sheet.
Read the formula in the formula bar and look for a reference to the cell itself, or to a range that includes it.
After you fix it, check the Circular References list again, because Excel shows one loop at a time. Repeat until the status bar no longer shows "Circular References".

2. Change the range so it leaves out the formula's own cell

Select the cell Excel flagged.
In the formula bar, find the range that includes this cell, for example B2:B10 in a formula that sits in B10.
Change the range to stop one cell before the formula, for example =SUM(B2:B9).
If the formula uses a whole column (such as C:C), either move the formula to a different column or use a fixed range such as C2:C500.
Press Enter and check that the status bar no longer shows Circular References.

3. Follow a loop that runs through several cells with Trace Precedents

Excel desktop on Windows and Mac

Select one of the cells listed under Formulas > Error Checking > Circular References.
Click Formulas > Trace Precedents. Blue arrows show which cells feed this formula. A dashed line with a sheet icon means an input is on another sheet.
Click Trace Precedents again to go back another level, or use Trace Dependents to see which cells use this one.
Find the step where the chain comes back to the starting cell, and change that formula so it uses a hard value or a different cell.
Click Formulas > Remove Arrows when you are done.

4. Check hidden sheets when no cell address is shown

Right-click any sheet tab and choose Unhide. If the option is grayed out, no sheets are hidden.
Unhide each sheet listed, then go to Formulas > Error Checking > Circular References again.
Alternatively, press Ctrl+G (Control+G on Mac), type the cell address from the list including the sheet name (for example Sheet3!D14), and press Enter to jump straight to it.
Fix the formula, then hide the sheets again if needed.

5. Allow the circular reference with iterative calculation (only if the loop is intended)

Windows: File > Options > Formulas. Mac: Excel > Settings (Excel > Preferences on older macOS) > Calculation.

On Windows, go to File > Options > Formulas. On Mac, go to Excel > Settings (or Excel > Preferences) > Calculation.
Under Calculation options, check Enable iterative calculation (on Mac: Use iterative calculation).
Leave Maximum Iterations at 100 and Maximum Change at 0.001, or change them for your model. Excel stops after that many rounds, or once values change by less than that amount.
Click OK. The warning stops and Excel calculates the loop repeatedly until the values settle.
Use this only for loops you built on purpose. It also hides accidental circular references, so totals can be wrong without any warning.

Next time, skip the search

Crade is an AI assistant for Mac and Windows that sees your screen. When a message like this pops up, ask “what does this mean?” and it explains the exact error in front of you, step by step.

Download Crade

Still not fixed?

If the warning still appears when the file opens but Circular References lists nothing, save the file, close Excel completely, and reopen it so Excel checks all the formulas again. Next, check any linked workbooks (Data > Edit Links) and the formulas behind named ranges (Formulas > Name Manager), because a loop can run through either of them. If the workbook came from someone else, ask the author whether it was built to use iterative calculation. For shared business files, your IT or finance systems team can review the model's formula dependencies.

Questions people ask

How do I find a circular reference in Excel?

Go to Formulas > Error Checking (click the arrow) > Circular References, then click the cell address shown. The status bar at the bottom of the Excel window also shows "Circular References" followed by the address of one affected cell.

Why does the circular reference warning appear every time I open the file?

The workbook still contains a circular reference, often on a hidden sheet or on a sheet you are not looking at. Find it with Error Checking > Circular References and fix it, or turn on iterative calculation if the loop is intentional.

Why is my formula showing 0 after the circular reference warning?

Excel does not calculate a loop unless iterative calculation is on, so the cell keeps 0 or its last value. Once the formula no longer refers to its own cell, it calculates normally.

Is it safe to enable iterative calculation?

It is safe if you built the loop on purpose, for example in some interest or tax models. It is not a fix for an accidental loop, because it turns off the warning and the result may be wrong.

Why is Circular References grayed out under Error Checking?

Excel currently sees no circular reference in the active workbook, or the loop is in a different open workbook. Switch to the other workbook and check again, or close and reopen the file so Excel checks every formula again.

Sources

Related errors