To show a circular reference in Excel for Microsoft 365 on Windows, select Formulas > Error Checking > Circular References. Choose a listed cell address to jump to it; then inspect the formula and its references to find the loop.
What a circular reference means
A circular reference occurs when a formula depends on its own result, either directly or through another cell. Excel cannot calculate a normal answer from a dependency that loops back to itself. For example, a formula in A1 that refers to A1 directly creates a circle. A longer chain can do the same thing: A1 depends on B1, while B1 depends on A1.
For a simple two-cell example, assume A2 and B2 are blank cells. Enter =B2+1 in A2, then enter =A2-1 in B2. Each cell now needs the other cell’s value to calculate, so the pair forms a circular reference. The numbers in this example are only there to demonstrate the dependency; they do not produce a useful calculation.
Excel usually warns when you enter or change a formula that creates a circular reference. The warning may identify a cell, but it does not necessarily explain the full chain or tell you which formula was intended. Existing workbooks can also contain a circle that is easy to miss, especially if the formulas span multiple sheets.
How to show the circular-reference cell
In the Windows desktop version of Excel for Microsoft 365, use the ribbon to locate a detected circular reference:
- Open the workbook and select the Formulas tab.
- In the Formula Auditing area, open Error Checking.
- Point to or open Circular References in the menu.
- Select a cell address from the list. Excel moves the active selection to that cell.
If the Circular References item is unavailable or no address appears, Excel may not currently have a detected circular reference in the workbook, or the circle may have been corrected already. Check the status bar at the bottom of the Excel window as well: Excel can display a circular-reference notice there. The ribbon menu is generally more useful for navigating to a listed cell.
After selecting a listed cell, read its formula in the formula bar. A direct circle is often visible immediately because the formula refers to its own address. For an indirect circle, note the cells it uses and follow those references until you find the one that leads back to the starting cell. The menu helps locate a cell in the loop, but it does not automatically rewrite the formulas or determine the intended business logic.
Trace the cells that make up the loop
Use Excel’s formula-auditing tools to inspect how the selected cell is connected to other cells. Select the formula cell, then choose Formulas > Trace Precedents to display arrows to cells that feed its value. You can select a referenced cell and trace its precedents in turn. If the arrows extend across worksheets, Excel may show a worksheet indicator rather than a complete on-sheet arrow; inspect the referenced formula or use the listed cell address to continue.
Trace Dependents, also on the Formulas tab, shows cells that use the selected cell. It can help you understand what might be affected by a correction, though it does not by itself identify the full circular chain. To remove arrows, use Formulas > Remove Arrows.
For a large workbook, it can help to write the dependency chain down as cell addresses and sheet names. A sequence such as Summary!B4 to Costs!D9 to Inputs!C3 and back to Summary!B4 makes the loop easier to see. Check named ranges too: a formula may use a name rather than an obvious cell address, and the name’s definition can point into the circular chain.
Common causes and easy-to-miss mistakes
A frequent cause is a range that includes the formula cell itself. For example, a total formula entered inside the range it is meant to add may try to include its own result. A similar mistake can happen when a copied formula’s range shifts one row or column farther than intended. Review the start and end of every range, especially in totals, subtotals, and templates copied across periods.
- Self-reference: a formula directly refers to the cell containing it.
- Two-cell or multi-cell loop: one formula depends on another formula that eventually points back.
- Copied formula error: relative references move during copying and create an unintended link.
- Named-range loop: a defined name refers to a formula or range that leads back to the original cell.
- Cross-sheet dependency: a formula on one worksheet feeds a chain that returns to the first worksheet.
- Table or total-row confusion: a formula is placed inside a range that includes its own cell.
A circular reference is not the same as a formula that displays an error such as #VALUE! or #REF!. Those errors can have other causes. Likewise, a formula can be wrong without being circular: if it references the wrong cell but that cell does not lead back to it, there is no circular dependency for Excel to list.
How to remove an unintended circular reference
First decide what the formula is supposed to calculate. Do not simply delete a cell or replace a formula with a guessed value; that can remove the warning while damaging a total or dependent calculation. Compare the formula with a working row or column, check the intended input cells, and confirm whether the formula’s range should include the cell where the formula sits.
Then correct the reference or move the formula outside the range it summarizes. If two formulas refer to each other because an input and a result were placed in the wrong cells, separate the input from the calculated output. When a formula was copied, compare its references with the neighboring formulas and adjust the range or use the appropriate absolute reference if the calculation requires a fixed row or column.
After making a change, revisit Formulas > Error Checking > Circular References and check whether any addresses remain. Recalculate the workbook and inspect important outputs, not just the cell that triggered the warning. A workbook can have more than one circular reference; correcting one may reveal another. Save a copy before substantial repairs, particularly when the workbook is a shared model or contains formulas that are difficult to reconstruct.
When iterative calculation is appropriate
Some models intentionally use circular formulas to represent calculations that depend on one another, such as a simplified model where interest affects a balance and the balance also affects interest. In such cases, Excel can repeatedly recalculate formulas until it reaches a stopping condition. This is called iterative calculation. It is different from fixing a mistaken circular reference: enabling it tells Excel to calculate a loop rather than remove the loop.
On Windows, open File > Options > Formulas, select Enable iterative calculation, and review Maximum Iterations and Maximum Change. Maximum Iterations sets the limit on calculation passes. Maximum Change sets the threshold used to decide whether successive results have changed enough to continue. A smaller change threshold can require more work; a low iteration limit can stop before a result has settled. The appropriate values depend on the model and its required precision.
Do not turn this setting on just to hide a warning. Iterative results may depend on calculation settings, starting values, and how the workbook is designed. If the model is intended to converge, document the reason for the circularity and test representative cases, including cases where values change significantly. If you did not design the workbook, ask its owner before changing iteration settings.
Excel for Mac and Excel for the web
The ribbon instructions above are for Excel for Microsoft 365 on Windows. Excel for Mac has a Formulas tab and formula-auditing features, but the exact placement of commands and calculation preferences can vary with the release. Look for Error Checking and Circular References on the Formulas tab. On Mac, calculation preferences are generally accessed through Excel > Settings or Excel > Preferences, depending on the version, then the calculation options. The Windows path File > Options does not apply to Mac.
Excel for the web has a different interface from the desktop app, so the Windows ribbon path may not appear exactly as written. If you cannot locate or trace the circular-reference list in the browser, open the workbook in the desktop Excel app and use the Formulas tab. When switching between desktop and web, verify the workbook’s results and calculation behavior rather than assuming every diagnostic or calculation control is in the same place.
Frequently Asked Questions
Where does Excel show circular references?
On Windows desktop Excel, open Formulas > Error Checking > Circular References. Select a listed address to go to that cell. Excel may also show a circular-reference notice in the status bar.
Why is the Circular References menu empty?
Excel may not currently detect a circular reference in the workbook, or the issue may already have been corrected. Check the workbook’s status bar and confirm that you are viewing the workbook and worksheet where the formula is located. If needed, inspect formulas and named ranges that connect across sheets.
Can a circular reference involve more than two cells?
Yes. A loop can pass through several formulas or worksheets before it returns to the starting cell. Trace precedents one cell at a time, noting each reference, until you find the link back to the original formula.
Should I enable iterative calculation to fix the warning?
No, not for an accidental circular reference. Iterative calculation is for models intentionally designed to recalculate a loop. Correct the formula or dependency instead unless you understand why the model needs iteration.
Will Excel automatically fix a circular reference?
No. Excel can warn about the problem and help you locate a cell, but it cannot know which formula or dependency matches your intended calculation. You need to inspect the logic and make the correction.
Is a circular reference always an error?
It is usually unintended in an ordinary worksheet, but some specialized models deliberately use circular calculations with iteration enabled. Whether it is appropriate depends on the model’s design, convergence, and required accuracy.
Related
- Compare Two Sheets in Excel: Methods That Work
- Excel Named Range: How to Create and Use One
- Merge Cell Rows in Excel: Steps, Limits, and Alternatives
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Solver Simplex: When to Use It and How It WorksExcel Solver’s Simplex LP method finds the best value for a linear objective while respecting linear constraints. Choose…
- CSV Import Wizard in Excel: How to Import FilesTo import a CSV file into Excel for Microsoft 365 on Windows, choose Data > From Text/CSV, select…
- How to Build a Schedule in Excel: A Practical GuideTo build a schedule in Excel, choose a time period, put dates or time slots in row and…
- How to Double Space in Word 365: Text and ParagraphsTo double-space text in Word 365 on Windows, select the text—or press Ctrl+A to select the document—then choose…
- How to Keep Track of Your Budget in Excel: Step by StepTo keep track of a budget in Excel, record each transaction in a dated log, assign it a…
- Excel Import CSV with Line Breaks Without Splitting RowsTo import a CSV with line breaks inside a field without splitting its record across Excel rows, use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.