Circular Reference in Excel: How to Find and Fix It

Errors & fixes · Updated November 18, 2026

To get rid of a circular reference in Excel, edit the formula so it no longer refers to its own cell, either directly or through another formula. In Microsoft 365 for Windows, start with Formulas > Error Checking > Circular References to locate a reported cell, then correct its formula; enable iterative calculation only when the circular calculation is intentional.

What a circular reference means

A formula has a circular reference when its calculation depends on its own result. The dependency may be direct, as when a cell’s formula refers to that same cell, or indirect, when a chain of formulas eventually leads back to the starting cell. Excel ordinarily cannot calculate such a dependency as a simple one-way sequence, so it flags the circular reference and may show a warning or an unexpected value.

For example, if a formula is entered in B10 and includes B10 in the range it adds, B10 depends on its own total. A less obvious case is when A1 refers to B1 and B1 refers back to A1. Both cells are part of a loop, even though neither formula mentions itself by name. Circular references can also involve multiple sheets, which makes them harder to spot by looking at one worksheet alone.

Not every circular reference is a deliberate feature. A common cause is a range that accidentally includes the cell containing the total. Another is copying a formula and having a relative reference shift into the formula’s own cell. The important first step is to determine whether the loop is accidental or part of a calculation that is meant to repeat.

Find the circular reference Excel reports

On Excel for Microsoft 365 for Windows, use the circular-reference list to jump to a cell Excel has identified:

  1. Open the workbook and select the Formulas tab.
  2. Select Error Checking in the Formula Auditing group.
  3. Point to or select Circular References, then choose a listed cell address.
  4. Inspect the formula bar and the cell’s references. Correct the formula or follow its referenced cells to find the rest of the loop.

The menu may list more than one cell. Fix a listed reference and check the menu again, because another loop may remain. The status bar at the bottom of the Excel window may also display Circular References and a cell address. If the status bar identifies a cell, use that address to go directly to it; the exact display can depend on the workbook and Excel state.

A listed cell is a starting point, not always the whole explanation. If it refers to another formula cell, follow that dependency until you find the link that returns to the original cell. For a workbook with many sheets, check formulas on the referenced sheets too. A circular reference can cross worksheet boundaries, so inspecting only the active sheet may miss the source.

Fix a direct self-reference in a formula

The usual fix is to change the formula’s range or reference so the formula cell is excluded. Suppose B10 is meant to total the values above it, in B2 through B9, but the formula includes B10. With the total in B10 and the numbers in B2:B9, the mistaken formula is =SUM(B2:B10). Because the range includes B10 itself, Excel identifies a circular reference rather than an ordinary total.

With values 1, 2, 3, 4, 5, 6, 7, and 8 in B2:B9, change the formula in B10 to =SUM(B2:B9). The corrected formula returns 36: 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8. The example illustrates a useful check for total rows: confirm that the sum range ends immediately before the total cell, not on the total cell.

For a formula that directly adds its own cell, remove the self-reference and replace it with the input cell or range the calculation was supposed to use. Do not simply delete a reference without checking the intended calculation; that can remove a necessary input while making the warning disappear. If the formula was copied from another row, compare it with a nearby correct formula and check whether its references shifted as expected.

Track an indirect circular reference

When the circular-reference list identifies a cell but the formula looks reasonable, trace its dependencies. Select the cell, then choose Formulas > Trace Precedents to display arrows to cells used by the formula. Follow the arrows and inspect the formulas in those cells. You can use Formulas > Remove Arrows to clear the displayed arrows after investigating.

For example, imagine A1 contains a formula that uses B1, while B1 contains a formula that uses A1. Neither formula needs to contain its own address for the pair to form a loop: A1 needs B1, and B1 needs A1. To fix it, decide which cell should contain the original input and which should calculate from that input. Replace one link in the loop with the correct independent value or reference.

Trace arrows are a guide, not a substitute for reading the formulas. A long chain can make the return path easy to overlook, and formulas on other worksheets may require you to move between sheets as you investigate. Named ranges and structured table references can also hide the underlying cell reference. Check Formulas > Name Manager if a name in a formula might point back to the formula cell.

When iterative calculation is appropriate

Some models intentionally use a circular calculation that repeats until the result changes by a sufficiently small amount or Excel reaches a set iteration limit. In those cases, removing the loop may change the intended model. Excel’s iterative calculation setting allows such formulas to recalculate repeatedly, but it does not automatically prove that the calculation is logically correct.

To turn on iteration in Microsoft 365 for Windows, go to File > Options > Formulas, select Enable iterative calculation, and review the settings for maximum iterations and maximum change. These settings control when Excel stops repeating the calculation. A higher iteration limit can allow more recalculations; the maximum-change setting defines how small a change must be before Excel treats the result as settled. Choose values appropriate to the model rather than changing them just to silence a warning.

Iteration is a workbook calculation setting, not a repair for a mistaken formula. Enabling it can affect other circular formulas in the workbook, and results can depend on the iteration settings. Before switching it on, confirm that the loop is intentional, that the formulas are designed to converge, and that the resulting values make sense. If you only need a one-time result, a clearer approach may be to calculate an input separately and enter it as a value, if that matches the purpose of the workbook.

Common causes and checks after a fix

Several ordinary editing mistakes create circular references. Check for these when Excel reports one:

  • A total includes itself: A SUM range extends through the cell containing the total.
  • A copied formula shifted: Relative references moved into the formula cell after a fill or copy operation.
  • A formula was entered in the wrong cell: A formula intended for a neighboring row or column was placed inside its own input range.
  • Two or more formulas depend on each other: A circular chain crosses cells, sheets, or named ranges.
  • A pasted formula brought a dependency with it: A copied section now refers back to a cell that depends on the section.

After editing a formula, check whether Excel’s circular-reference list is empty and whether the result is plausible. Recalculate the workbook if needed with Formulas > Calculate Now. In automatic calculation mode, Excel normally recalculates when inputs change, but calculation settings can be changed for a workbook. If a result remains stale, check Formulas > Calculation Options and confirm that the expected calculation mode is selected.

Also check for a second loop. Removing one circular reference does not guarantee that every circular reference in the workbook has been corrected. Review the circular-reference menu again, then inspect important totals and dependent results. If the workbook is shared or used for reporting, save a separate copy before making structural changes so you can compare the original formulas and values.

Windows, Mac, and Excel for the web

The steps above use Excel for Microsoft 365 on Windows. In Excel for Mac, the ribbon’s Formulas tab includes formula-auditing tools, though ribbon placement can differ slightly by release. To change iteration settings, open Excel > Preferences > Calculation and look for the iterative-calculation controls. The wording and available controls can vary by Mac version.

Excel for the web can be useful for viewing and editing workbook formulas, but its available calculation and auditing controls may differ from the desktop app. If you cannot locate the circular-reference list or change iteration settings in the browser, open the workbook in the Microsoft 365 desktop application and use its Formulas tab or calculation preferences. Do not assume that a warning has been fixed just because it is not visible in the current view.

Prevent circular references in future work

Make total formulas easy to distinguish from the data they summarize. For example, keep totals in a separate row or column and verify the range endpoints before filling formulas down or across. When copying formulas, inspect the first and last copied cells, since a shifted relative reference at a boundary can create a loop that is not present in the middle of the range.

For more complex workbooks, label input cells and calculated cells clearly, and avoid making an input depend on a result that already uses that input. Use named ranges only when their definitions remain easy to understand, and periodically review names if a formula’s dependency is unclear. These habits make it easier to find the source of an error and help other people distinguish an intentional iterative model from an accidental circular reference.

Frequently Asked Questions

Why does Excel say there is a circular reference?
It has found a formula dependency that eventually leads back to the formula’s own cell. The loop may be direct, such as a total including itself, or indirect, with two or more cells depending on one another.

How do I find the cell causing a circular reference?
In Excel for Microsoft 365 on Windows, select Formulas > Error Checking > Circular References and choose a listed address. Follow the cell’s references until you find the dependency that returns to it.

Can I ignore a circular reference warning?
Only if the circular calculation is intentional and the workbook is designed to use iterative calculation. Otherwise, ignoring it can leave results incomplete or misleading. Confirm the formulas and test the output before relying on the workbook.

Does enabling iterative calculation fix every circular reference?
No. It permits Excel to recalculate circular formulas repeatedly; it does not correct an accidental loop or guarantee that an intentional model converges to a useful result. Review the formulas and iteration settings.

Why can’t I see the circular-reference option?
The list may be unavailable when Excel has not detected a circular reference, or the controls may differ in your Excel version or platform. Open the file in the Microsoft 365 desktop app and check the Formulas tab; on Mac, calculation preferences are under Excel > Preferences > Calculation.

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.