In Excel for Microsoft 365 on Windows, go to Formulas > Error Checking > Circular References to see cells involved in a circular reference; select a listed cell to jump to it. Then inspect its formula and the cells it depends on, and change the formula chain so it no longer leads back to itself—or deliberately enable iterative calculation if the loop is intended.
What a circular reference means
A circular reference occurs when a formula depends, directly or indirectly, on its own result. A direct loop is easy to recognize: a formula in a cell refers to that same cell. An indirect loop involves two or more cells. For example, a formula in B2 might depend on C2 while a formula in C2 depends on B2. Excel cannot calculate either value in the ordinary one-pass way because each value requires the other first.
This is different from a normal formula chain. If B2 depends on A2 and C2 depends on B2, Excel can calculate A2, then B2, then C2. The issue arises when the dependency chain eventually returns to a cell it already needs to calculate. A circular reference may be accidental, such as a SUM range that includes its own total cell, or intentional in a model that repeatedly refines an estimate.
Find the circular reference with Error Checking
On Windows desktop Excel, use the ribbon path Formulas > Error Checking > Circular References. Excel lists detected circular-reference cells in that menu. Choose an entry to move to the corresponding cell, then inspect the formula bar and the surrounding worksheet. If the menu is unavailable, first make sure the workbook is open in desktop Excel and that calculation has run.
- Open the workbook and select the Formulas tab.
- In the Formula Auditing group, open Error Checking.
- Point to or open Circular References and select a cell reference in the list.
- Review the formula in the selected cell and identify the other cells it uses.
- Correct the dependency and check the menu again. Excel may reveal another loop after the first one is removed.
The status bar at the bottom of the Excel window can also report that a circular reference exists and may show a cell address. Treat that as a clue, not a complete report: a workbook can contain multiple loops, and the Error Checking menu is the more useful place to follow the listed references. The exact display can vary with the active sheet and Excel version.
Trace the cells that create the loop
Once you have selected a cell, use Excel’s formula-auditing arrows to understand its dependencies. Select the formula cell, then choose Formulas > Trace Precedents to show cells that feed into it. Choose Trace Dependents to show cells whose formulas use it. These tools are useful when a formula refers to another cell, which in turn refers to another, rather than directly to the original cell.
Follow the arrows through the worksheet and check each formula in the chain. If an arrow points to a cell on another sheet, the dependency crosses worksheets; inspect that sheet as well. A long chain can be difficult to read, so write down the cell addresses as you follow them. Use Remove Arrows in the Formula Auditing group when you are done. The arrows are visual guides; they do not change formulas or repair the loop automatically.
Check for indirect and cross-sheet references
Not every circular reference is obvious from the selected cell. A formula may refer to a named range, a cell on another worksheet, or a range that includes the formula cell. For example, a total placed inside the range it is meant to add can make the total depend on itself. Check the range boundaries, not just the formula’s visible starting cell.
Cross-sheet loops can be particularly easy to miss. A formula on a summary sheet may depend on a detail sheet, while a formula on that detail sheet points back to the summary. Follow precedents until the chain returns to a cell you have already visited. Also check defined names in Formulas > Name Manager if the formula uses a name instead of an ordinary cell address. A named range can conceal an unexpected reference, although the name itself is not necessarily the cause.
Fix an accidental circular reference
Choose a repair that matches the intended calculation. Do not simply remove a reference without confirming what the result is supposed to represent. Common repairs include moving a total outside its input range, correcting a mistaken row or column reference, and separating an input from a calculated output.
- Move a total cell: Put the total below or beside the values it adds, outside the sum range.
- Correct a copied formula: Check whether a relative reference shifted into the formula’s own cell when the formula was filled across or down.
- Separate inputs and outputs: Keep a manually entered assumption in one cell and the formula based on it in another. Avoid typing a new value over a formula that is meant to calculate that value.
- Review ranges and names: Exclude the result cell from a range and confirm that any named range points to the intended cells.
- Break an unintended cross-sheet link: Decide which sheet should supply the value, then remove the return dependency from the other sheet.
After a change, return to Formulas > Error Checking > Circular References and confirm the list is clear. Then check that key outputs still make sense. Removing a loop can expose a separate problem, such as a wrong reference or a blank input, so verify the calculation rather than stopping at the disappearance of the warning.
When iterative calculation is appropriate
Some specialized worksheets intentionally use a circular calculation that improves through repeated passes. In that case, Excel can use iterative calculation: it recalculates the formulas until it reaches a stopping limit. This is not a general-purpose fix for an accidental loop. If a workbook has an unintended circular reference, enabling iteration can hide the design error and produce values that are difficult to interpret.
To configure it in Excel for Microsoft 365 on Windows, choose File > Options > Formulas, select Enable iterative calculation, and review Maximum Iterations and Maximum Change. Maximum Iterations limits how many recalculation passes Excel performs; Maximum Change sets the change threshold at which Excel can stop. A smaller change threshold can require more work, and the appropriate values depend on the model.
Before enabling iteration, document why the loop is needed and test the workbook with representative inputs. Check whether results settle consistently and whether changing the starting values affects the answer. Iteration settings can affect calculation across the workbook, not just the cell that first raised the warning. For a shared file, tell other users that the model relies on iterative calculation. On Mac, look under Excel > Preferences for calculation settings; labels and locations can vary by release. Excel for the web may not expose the same full settings, so use desktop Excel when you need to configure or investigate iteration.
Common causes and troubleshooting
A circular reference warning does not always mean the formula currently selected is visibly self-referential. The loop may be several cells long, hidden on another sheet, or created by a range that expanded to include the output. If the warning persists after you edit one cell, check the Circular References menu again: there may be more than one loop, or the edit may not have broken the entire dependency chain.
- The menu appears empty: Recalculate the workbook, check other worksheets, and look for a status-bar warning. If you are using Excel for the web, open the file in desktop Excel for the fuller auditing tools.
- A formula shows zero or an old value: That display does not prove the loop is fixed. Excel’s behavior can depend on calculation settings and previously calculated values. Inspect the dependency chain and the workbook’s iteration setting.
- The warning returns after copying formulas: Review relative and absolute references. A formula copied to a new location can shift a reference into its own cell or into a loop.
- You cannot find a direct self-reference: Trace precedents through other cells and sheets, and inspect named ranges and range endpoints.
Also distinguish a circular reference from an ordinary formula error. Errors such as #VALUE! or #REF! have different causes; a circular reference is about a dependency loop, even if it also leads to an unhelpful displayed result. Correcting a separate error may be necessary, but it does not necessarily remove the loop.
Excel desktop, Mac, and web differences
The steps above are written for Excel for Microsoft 365 on Windows. Excel for Mac includes formula-auditing tools, but the options may appear in different locations or use slightly different labels depending on the release. Look on the Formulas tab for Error Checking and auditing commands; use Excel’s calculation preferences to review iteration settings. Keyboard shortcuts are not necessary for finding the reference, and shortcuts can differ between Windows and Mac.
Excel for the web can be useful for viewing and editing a workbook, but its ribbon and calculation controls do not always match the desktop application. If you cannot see the Circular References list or need to change iteration settings, open the workbook in the desktop app. Avoid assuming that a file’s displayed values prove that every dependency is valid: cached or previously calculated results can remain visible while the formulas still need attention.
Frequently Asked Questions
Where is Circular References in Excel?
On Windows desktop Excel, select Formulas > Error Checking > Circular References. Choose a listed cell address to jump to it.
Why does Excel say there is a circular reference when I cannot see one?
The loop may involve several cells, a different worksheet, a named range, or a formula range that includes its own result cell. Trace precedents and follow the chain until it returns to a cell already visited.
Will enabling iterative calculation fix every circular reference?
No. Iteration is for models designed to recalculate a loop repeatedly. It can conceal accidental formula errors, so first decide whether the circular dependency is intentional.
Can a circular reference be intentional?
Yes. Some models use repeated calculations to converge on an estimate. Such a workbook needs deliberate iteration settings, testing, and clear documentation; an accidental loop should instead be redesigned.
How do I remove a circular reference without changing the intended result?
Identify the calculation the sheet is meant to perform, then correct the dependency structure—for example, move a total outside its input range or separate an input from its calculated output. Verify the resulting values after the loop is gone.
Related
- Excel Data Validation: Add Criteria and Rules
- Excel Data Table With 3 Variables: Limits and Workarounds
- Excel Data Table With Multiple Outputs: How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Find and Replace: Search, Change, and Fix TextTo find and replace text in Excel for Microsoft 365 on Windows, press Ctrl+H, enter the text to…
- Excel Insert Page Break Not Working? Fixes That HelpIf Excel’s Insert Page Break command is unavailable or the break does not appear where you expect, first…
- Excel: Remove #N/A Errors From VLOOKUP Results SafelyTo replace a VLOOKUP #N/A error with a readable message, wrap the lookup in IFNA: =IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found"). This…
- Power Query Filter Not Working? Fix Common CausesIf a Power Query filter is not working, first check the filter step in the Power Query Editor,…
- 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…
- Excel Circular References: How They Work and Fix ThemAn Excel circular reference occurs when a formula refers back to its own cell, either directly or through…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.