Remove a Circular Reference in Excel: Find and Fix It

Errors & fixes · Updated November 21, 2026

To remove a circular reference in Excel for Microsoft 365 on Windows, go to Formulas > Error Checking > Circular References, select a listed cell, and edit its formula so it no longer depends on itself. If the loop is intentional, you can instead enable iterative calculation in File > Options > Formulas, but do that only when you understand how the repeated calculation affects the result.

What a circular reference means

A circular reference occurs when a formula refers, directly or indirectly, to the cell that contains it. Excel cannot calculate an ordinary one-pass result for that dependency loop: the value of the cell depends on a value that, in turn, depends on the first cell. Excel usually warns about the problem when you enter or change the formula, although a loop can be less obvious in a large workbook.

A direct example is a formula in A1 that refers to A1 itself. With A1 as the cell containing the formula, =A1+1 is a circular reference. An indirect loop involves multiple cells: a formula in A1 can depend on B1 while a formula in B1 depends on A1. The cells do not have to be adjacent, and the path can pass through several worksheets.

Circular references are not the same as a formula that uses a cell containing a previous result. The issue is whether the current calculation chain leads back to the formula cell. Excel can calculate intentional circular models by repeating calculations when iterative calculation is enabled, but an accidental loop should normally be corrected rather than accepted.

Find the circular reference in Excel

On Windows, use the built-in error-checking list to jump to a cell Excel has identified:

  1. Open the workbook in the Excel desktop app and select the Formulas tab.
  2. Open the arrow beside Error Checking in the Formula Auditing group.
  3. Point to Circular References. Choose a cell address from the submenu to go to that cell.
  4. Inspect the formula bar and the surrounding inputs to determine which reference closes the loop.

Excel may show a circular-reference notice when a problematic formula is entered. The status bar at the bottom of the window can also show a circular-reference message and a cell address. If the workbook contains more than one loop, use the menu to visit the listed references and keep checking after each correction: removing one cycle may reveal another. The precise display can vary with workbook state, so do not rely on the warning dialog alone.

If the Circular References menu is unavailable or you cannot identify the cause, check whether the workbook is in a state where Excel has already recorded an error or whether the loop is on a different sheet. Save a copy before making substantial repairs. You can also select a cell and inspect the formula bar, or use the Name Box to navigate to a cell address shown in a warning.

Remove the loop by correcting the formula

The right repair is usually to change the formula’s range or reference so it uses inputs only, not its own result. For example, suppose B2:B6 contains five expense amounts: 25, 30, 35, 20, and 40. B7 is intended to total those five entries. If the formula is entered in B7, including B7 in the sum creates a loop:

With expenses in B2:B6 and the total formula in B7, this incorrect formula includes its own result cell: =SUM(B2:B7). Excel flags the circular reference rather than producing a dependable total.

With the same layout, the corrected formula excludes B7: =SUM(B2:B6). It returns 150, because 25 + 30 + 35 + 20 + 40 = 150.

This kind of range-boundary mistake is common in totals, subtotals, and reports. Other fixes depend on the intended logic. You might move a formula to a separate output cell, replace a reference to a calculated result with a reference to its original input, or break a two-cell dependency by separating an intermediate calculation. Do not just delete a reference at random: confirm what the formula is supposed to calculate and check that the revised result makes sense.

Trace indirect and hard-to-find loops

When the listed cell’s formula does not appear to refer to itself, follow its dependencies. On the Formulas tab, use Trace Precedents to see cells that feed the selected formula and Trace Dependents to see cells that rely on it. Follow the arrows through the calculation until you find a path that returns to the starting cell. Select Remove Arrows in the Formula Auditing group when you have finished inspecting them.

Tracing is especially useful when a formula points to another formula rather than to a raw input. For example, a report total might depend on a subtotal, which depends on a percentage cell, which in turn depends on the report total. That is an indirect circular reference even if the first formula never names its own cell. Name ranges can also hide the path; use Formulas > Name Manager to inspect names used in a formula when a reference is not obvious.

For a large workbook, work systematically. Start with the cell listed by Excel, note every referenced cell, and inspect the formulas in those cells until the chain ends at genuine inputs or returns to the starting point. If formulas are difficult to see in a dense sheet, Formulas > Show Formulas displays formulas in cells instead of their calculated values. You can also inspect one formula at a time in the formula bar; avoid changing several linked cells before recording the original logic.

When a circular reference is intentional

Some models deliberately use a circular relationship. For instance, a calculation might determine interest from an average balance while that balance also reflects the interest amount. In such a model, repeated recalculation can approach a stable value. This is a specialized modeling choice, not a general-purpose way to dismiss Excel’s warning.

To permit iteration in Excel for Microsoft 365 on Windows, select File > Options > Formulas, check Enable iterative calculation, then review Maximum Iterations and Maximum Change. Maximum Iterations limits how many calculation rounds Excel performs; Maximum Change sets a tolerance for when the result is considered to have settled. Excel’s commonly used defaults are 100 iterations and 0.001 maximum change, but check the actual settings in the workbook before relying on them.

Iteration can produce an approximate result rather than a simple exact answer. The result may depend on starting values, calculation settings, and whether repeated calculations converge. If values oscillate or fail to settle, allowing more iterations does not necessarily solve the underlying model problem. Document why the loop exists, test the workbook with representative inputs, and check that formulas elsewhere still work as intended. If the loop was accidental, turning iteration on can conceal the defect instead of fixing it.

Common causes and troubleshooting

  • A total includes its own cell: Check the first and last cells in SUM ranges, especially when rows are inserted or formulas are copied.
  • A copied formula shifted unexpectedly: Relative references change when formulas are filled across or down. Review the references in the copied cell and compare them with a correct neighboring formula.
  • Two or more formulas refer back to one another: Trace the chain through intermediate calculations rather than looking only for a direct self-reference.
  • A named range or hidden sheet obscures a reference: Review Name Manager and inspect formulas on other worksheets. A workbook’s visible sheet may not contain every link.
  • A circular warning remains after an edit: Check for additional loops. Excel can report another circular reference after the first is removed, and a workbook may contain several independent cycles.
  • A result seems to change unexpectedly: Verify whether iterative calculation is enabled and whether the workbook is using formulas that intentionally rely on repeated calculation.

Also distinguish a circular reference from a wrong answer caused by an incorrect range, a stale value, or a formula error. A formula can be logically wrong without being circular. After correcting a loop, review the result against a small hand-checkable example and confirm that totals and dependent cells update appropriately.

Differences on Mac and in Excel for the web

In Excel for Microsoft 365 on Mac, the location of calculation preferences differs from Windows. Open Excel > Preferences > Calculation (called Settings in some current releases) to find the iterative-calculation option. Ribbon layouts can also vary by release, so use the Formulas tab and its error-checking or formula-auditing controls to locate and inspect the loop. A Windows-only path such as File > Options does not apply on Mac.

Excel for the web has a different interface and does not provide every desktop formula-auditing and calculation-setting control in the same way. If the loop is hard to locate, or you need to change iterative-calculation settings, open the workbook in the desktop version of Excel. Avoid assuming that a workbook’s behavior in the browser proves the calculation settings are correct; verify the model in the version used to maintain it.

How to prevent circular references

Clear spreadsheet structure makes accidental loops less likely. Keep raw inputs, intermediate calculations, and final outputs in distinct areas or columns, and make total ranges stop above or before their output cells. When copying formulas, check whether relative references moved as expected. For complicated models, label key inputs and calculated outputs so that a later editor can see which direction information is meant to flow.

Before distributing a workbook, check the Circular References menu and review warnings after major edits. If iterative calculation is required, record the reason and the chosen iteration limits in a note or documentation sheet. If a file behaves differently on another computer, compare its calculation settings and Excel version before assuming the formulas themselves changed.

Frequently Asked Questions

How do I find a circular reference in Excel?
In Windows desktop Excel, select Formulas > Error Checking > Circular References, then choose a listed cell. Inspect its formula and follow the cells it refers to until you find the dependency that leads back to the original cell.

Can I remove a circular reference without deleting the formula?
Usually. Edit the formula so it refers to valid input or intermediate cells but not to itself through a chain of formulas. For example, correct a total range that accidentally includes its own output cell. Keep the formula if its purpose is still needed; change the dependency that creates the loop.

Why does Excel say there is a circular reference when I cannot see one?
The reference may be indirect, pass through another worksheet, or be hidden behind a defined name. Use Trace Precedents and inspect the formulas of referenced cells. A loop can involve several cells, so the formula that Excel lists may not contain an obvious self-reference.

Is a circular reference always a mistake?
No. Some iterative financial or engineering models intentionally use a circular relationship. For those cases, iteration may be appropriate after the model is understood and tested. For ordinary totals, budgets, and reports, a circular reference is more often an accidental formula or range error.

Will enabling iterative calculation fix the circular reference?
It allows Excel to recalculate intentional loops repeatedly; it does not repair the formula logic. If the cycle is unintended, correct the formula instead. If iteration is needed, review the iteration count and tolerance, and verify that the result converges and remains sensible.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.