If Excel keeps showing a circular reference warning, a formula is referring to its own cell either directly or through a chain of other formulas. In Microsoft 365 for Windows, start with Formulas > Error Checking > Circular References, select a listed cell, and then change the formula so its calculation no longer loops; enable iterative calculation only when the loop is intentional.
What a circular reference means
A formula normally takes its inputs from other cells and returns an answer. A circular reference occurs when that chain eventually leads back to the cell where the calculation started. Excel cannot determine a normal answer from a loop unless iterative calculation is enabled, so it warns you and may show an incomplete or outdated value.
The simplest case is a formula entered into a cell that refers to that same cell. A less obvious case involves several cells: A1 depends on B1, B1 depends on C1, and C1 depends on A1. The cells do not have to be adjacent, and the loop can cross worksheets. A workbook can also contain more than one circular reference.
A circular reference is not the same as a formula error such as #VALUE! or #REF!. It describes a dependency problem: Excel has been asked to calculate a value using a result that depends, directly or indirectly, on that same value.
Find the cell or formula causing the warning
On Windows, use the ribbon’s Formulas tab and open Error Checking > Circular References. If Excel has identified a loop, the menu lists a cell address; select it to go to that cell. The status bar may also display Circular References and an address. If the menu is empty or the warning persists, check other worksheets and the status bar while moving between sheets.
- Select the listed cell and inspect its formula in the formula bar.
- Use Formulas > Trace Precedents to see cells the formula uses, and Trace Dependents to see formulas that rely on it. Follow the arrows through the calculation chain.
- Check formulas in the cells along that chain, including formulas on other sheets. A reference such as Sheet2!B4 can hide the return path.
- When you find the loop, decide whether it is accidental or deliberately part of the model before changing anything.
Excel generally reports one circular-reference location at a time, so resolving the first loop may reveal another. Repeat the check after each repair. If the workbook is large, search formulas for references to the cells involved and inspect named ranges or formulas copied across a range, which can make a return path less obvious.
Common ways a loop gets into a workbook
One frequent cause is including the formula cell in its own total. Suppose C10 is meant to total values in C2:C9, but the selected range extends through C10. The total now includes itself. With values 10, 20, 30, 40, 50, 60, 70, and 80 in C2:C9, the intended formula layout is: values in C2:C9 and the total in C10. Enter =SUM(C2:C9) in C10; it returns 360 and excludes the result cell.
Other common sources include:
- A copied formula whose relative references shifted back to its own cell.
- A formula that refers to a total cell which, in turn, includes the original formula cell.
- A worksheet link that creates a loop between sheets, such as a summary feeding a detail calculation that feeds the summary.
- A name defined in Formulas > Name Manager that points to a cell or formula involved in the loop.
- A formula pasted over an input cell, changing an intended input into a calculation that depends on itself.
For example, if you put a formula in B2 that refers to B2, the cell has a direct loop. If you meant to add ten percent to a value stored in A2, use this layout instead: A2 contains 120, and B2 is the output cell. Enter =A2*1.1 in B2; it returns 132. Keep the source input separate from the calculated result.
Fix the formula without changing the intended calculation
Before editing, identify what the formula is supposed to calculate and which cells are true inputs. Then break the loop at the point where the formula refers back to its own output. Often the fix is to adjust a range boundary, redirect a reference to an input cell, or move the formula to a separate result cell.
For a total that accidentally includes itself, exclude the total cell from the range, as in the C10 example. For a percentage or markup, keep the original amount in one cell and calculate the adjusted value in another. For linked worksheets, decide which sheet should be the source of truth; changing a downstream reference may be safer than altering the original input.
After making a change, check the result against a small hand calculation or a known example. Also inspect nearby formulas: correcting one cell can leave inconsistent references in the rest of a copied formula range. Save the workbook, recalculate it, and revisit Formulas > Error Checking > Circular References to see whether Excel reports another loop.
When iterative calculation is appropriate
Some specialized models intentionally use circular formulas to refine an estimate repeatedly. In that case, Excel’s iterative calculation can calculate the loop by using results from successive calculation passes until it reaches a stopping condition. It is not a general-purpose fix: enabling it can make accidental circular references produce plausible-looking numbers instead of drawing attention to the underlying mistake.
On Windows, go to File > Options > Formulas, then under Calculation options select Enable iterative calculation. The controls let you set Maximum Iterations and Maximum Change. Iterations limit the number of passes; maximum change sets a tolerance for how much the result may change before Excel stops. A tighter tolerance can require more passes, while a low iteration limit can stop before the value has settled.
Use iteration only when the model’s logic specifically requires it, and document why. Test the workbook with representative inputs, check that results stabilize, and compare them with an independent calculation if possible. Different iteration settings can affect the output. On Mac, the corresponding controls are under Excel > Settings (called Preferences in some releases) > Calculation. Excel for the web may not expose the full desktop controls, so use the desktop app to review or change those settings.
Why the warning may keep returning
If the warning appears again after you fix a formula, the workbook may contain another loop, or a formula may have been copied or changed since the repair. A warning can also appear when you open a workbook created on another computer: that file may use iterative calculation, or its formulas may contain a circular reference that was not resolved on your system.
Check whether iteration is enabled before assuming that the workbook is fixed. When iterative calculation is on, Excel may calculate a circular formula rather than show the same warning, but the circular dependency still exists. Conversely, turning iteration off does not remove the loop; it only changes how Excel handles it.
If the circular-reference menu does not make the path clear, temporarily inspect the formulas feeding the reported cell, and follow references across sheets. Be cautious with deleting formulas or clearing cells to silence the warning: those actions can remove important model logic without correcting the dependency. Save a copy before making structural changes to a shared or important workbook.
Excel desktop, Mac, and web differences
The core cause is the same across Excel versions, but the settings paths differ. In Microsoft 365 for Windows, use Formulas > Error Checking > Circular References to locate a reported cell, and File > Options > Formulas to configure iteration. On Mac, Excel’s ribbon offers formula-checking tools, while calculation settings are found through Excel > Settings or Preferences > Calculation, depending on the release.
Excel for the web can open and calculate workbooks, but its controls are not identical to desktop Excel, particularly for calculation settings. If you need to change iteration settings or investigate a complex workbook, open it in the desktop app. Menu labels can vary slightly between releases, but the underlying diagnosis remains: follow the formula dependencies until you find the path back to the starting cell.
Frequently Asked Questions
Can I ignore an Excel circular reference warning?
Only if the circular calculation is intentional and the workbook has been designed and checked to use iterative calculation. Otherwise, treat the warning as a sign that a formula or dependency needs attention.
Why does Excel show a circular reference when I cannot find one?
The loop may run through multiple cells, another worksheet, or a defined name. Use the Circular References list, select the reported cell, and trace its precedents and dependents through the full chain.
Will turning on iterative calculation fix the formula?
No. It tells Excel to calculate circular formulas repeatedly, which is useful for some deliberate models but does not remove the loop. For an accidental loop, correct the references instead.
Why does the circular reference return after I close and reopen the file?
The formula may not have been corrected, another loop may remain, or the workbook may be using iterative calculation. Recheck the circular-reference list and calculation settings in the desktop app.
Can a circular reference involve more than one worksheet?
Yes. A formula on one sheet can depend on a cell on another sheet that ultimately depends on the first formula. Follow the references across sheets rather than checking only the original cell.
Related
- How to Add Headers in Excel on SharePoint and Format Them
- Changing Date Format in Excel: Steps, Options, and Fixes
- How to Add the Same Header in Excel on Every Sheet
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- SUMIF Formula Not Working? Fix Common Excel ErrorsIf SUMIF is returning zero or the wrong total, check that its criteria range, criterion, and sum range…
- Excel Spill Error: Causes, Fixes, and Clear ExamplesTo fix an Excel spill error, select the cell showing #SPILL!, identify what is blocking the formula’s intended…
- XLOOKUP Excel IF ERROR: Fix Missing Matches and ErrorsTo show a friendly result when XLOOKUP cannot find a match, use its fourth argument: with lookup value…
- Excel File Won’t Print? Fix Page Setup and Printer IssuesIf an Excel file won’t print, first choose the correct printer in File > Print, then check the…
- Excel Goal Seek Not Working? Fix These Common ProblemsIf Excel Goal Seek is not working, first confirm that the Set cell contains a formula linked to…
- Excel Filter Not Working After a Certain Row? Fix ItIf Excel filtering stops at a certain row, the filter range usually ends there—often because a completely blank…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.