Locate Circular References in Excel: Find and Fix Them

Errors & fixes · Updated November 21, 2026

In Excel for Microsoft 365 on Windows, select Formulas > Error Checking > Circular References to see listed cells that participate in a circular reference; select an address to jump to it. You can also look for a Circular References notice on the status bar, then trace the affected cells to find the loop and decide whether to correct it or intentionally allow iterative calculation.

What a circular reference means

A circular reference occurs when a formula refers, directly or through other cells, back to the cell containing that formula. Excel cannot calculate an ordinary formula in a simple one-way sequence if the result depends on itself. A direct loop is easy to recognize, but a loop spread across several cells or worksheets can be less obvious.

For a direct example, with cell A1 selected and the formula entered in A1, the layout is that A1 refers to itself:

=A1+1

Excel identifies this as circular because calculating A1 requires knowing A1 first. A multi-cell loop can be harder to spot. With cell A1 holding the first formula and B1 holding the second, the layout is A1 depends on B1 and B1 depends on A1:

=B1+1 in A1, and =A1+1 in B1.

With iterative calculation off, Excel reports a circular reference rather than providing a dependable calculated result for this loop. The issue is about the chain of dependencies, not whether the formula looks syntactically valid.

Use Error Checking to locate the cell

Start with Excel’s built-in circular-reference list. Open the workbook in the desktop app, select the Formulas tab, and choose Error Checking > Circular References. If Excel has identified one or more loops, the menu shows cell addresses. Choose an address to move to that cell, then inspect its formula in the formula bar or in the cell itself.

  1. Save a copy of the workbook if you are about to make broad formula changes.
  2. Open Formulas > Error Checking > Circular References.
  3. Select a listed address and note the worksheet and formula.
  4. Check the other listed addresses, especially if the workbook contains more than one loop.
  5. Edit the formula or its inputs, then check the menu again to see whether the reference remains.

The menu is a locator, not a complete explanation of why the formula is circular. A listed cell may depend on another cell that eventually leads back to it, so inspect the surrounding formulas rather than changing only the first address you see. If the address is on a different worksheet, Excel may take you to that sheet when you select it. The status bar can also indicate that the workbook has a circular reference; depending on where the loop is, it may show an address or only the notice.

Trace the path through related cells

Once you reach a listed cell, use formula auditing to understand the dependency chain. Select the cell and choose Formulas > Trace Precedents to show cells that feed into its formula. Choose Formulas > Trace Dependents to show cells that use its value. These arrows can reveal the next link in a loop; follow them through the relevant cells until you find a path returning to the starting point.

For example, suppose a total in C10 uses a value from D4, while D4 is calculated from a result in C10. Neither formula needs to mention the other cell directly for the pair to form a circular reference. Inspect each formula along the chain, including formulas on other worksheets. If an arrow crosses a worksheet boundary or the diagram becomes crowded, select the cells one at a time and read their formulas rather than relying on a single visual trace.

To remove arrows after investigating, choose Formulas > Remove Arrows. This clears the auditing marks, not the formulas. It is often useful to write down the loop as a sequence of cell addresses before editing, particularly in a large model where several intermediate calculations are involved.

Why circular references happen

Many circular references are accidental. A formula may be entered in the wrong row, include its own total cell in a sum, or point to a summary that eventually uses the formula’s result. Copying a formula across a table can also shift a reference unexpectedly. In a large workbook, named ranges, hidden worksheets, and formulas that refer across sheets can make the cycle difficult to see at first glance.

  • A total includes itself: A sum placed in the range it totals may include its own cell.
  • A mistaken reference: A formula intended to use the previous row may refer to its own row instead.
  • A return path through another sheet: One sheet may feed a calculation that eventually feeds back to the first.
  • Copied formulas or changed ranges: Filling formulas or expanding a table can alter which cells are included.
  • An intentional model design: Some calculations deliberately use repeated estimates, but they require suitable iterative settings.

A common misconception is that every circular-reference warning means the workbook is broken. Usually it signals a formula design problem, but some financial or engineering models intentionally calculate values by repeated approximation. The important question is whether the loop was intended and whether the workbook’s calculation settings match that intent.

Fix an accidental circular reference

Choose a fix that restores the intended calculation order rather than merely hiding the warning. For a total that includes itself, move the total outside the summed range or correct the range endpoints. For a formula that points to the wrong row, repair the reference and check neighboring formulas for the same copying mistake. For a loop across sheets, decide which value should be an input and which should be an output, then remove the unintended return path.

When a formula is complicated, break its work into helper cells. This makes dependencies easier to inspect and can turn one opaque formula into a clear sequence of calculations. Before replacing a formula, compare it with the workbook’s intended logic and preserve a copy of the original. A circular-reference fix can remove a warning but still produce the wrong business result if it changes the meaning of the model.

After editing, reopen Formulas > Error Checking > Circular References. If the list is empty and the status bar no longer reports a loop, Excel no longer detects that circular reference. Recalculate the workbook and review important totals or outputs as well. Removing the loop confirms the dependency issue is gone; it does not by itself prove that every formula is correct.

When iterative calculation is appropriate

Excel can calculate an intentional circular model by repeating calculations, a process called iterative calculation. On Windows, go to File > Options > Formulas and select Enable iterative calculation. You can set Maximum Iterations, which limits the number of calculation cycles, and Maximum Change, which sets the threshold at which Excel considers successive results close enough to stop. These settings affect workbook calculation and should be chosen to suit the model, not simply enabled to dismiss a warning.

Iteration can be suitable when the workbook is designed to converge on a result through repeated updates. It is not a general repair for an accidental loop. Results may depend on the starting values, iteration limit, and tolerance; a poorly designed cycle can fail to settle or produce a misleading value. Keep a documented reason for using iteration and test the outputs against known cases or independent checks.

On Excel for Mac, look in Excel > Preferences for the calculation settings; the labels and layout can vary by release. Excel for the web does not provide the same full desktop formula-auditing workflow, so use the desktop app when you need to inspect circular-reference addresses and trace dependencies. If a workbook was created with iterative calculation enabled, verify its settings and results in the desktop version before relying on its calculations.

Frequently Asked Questions

Where is the Circular References command in Excel?
In Excel for Microsoft 365 on Windows, select Formulas > Error Checking > Circular References. The menu lists detected cell addresses; choose one to navigate to it.

Why can I see a circular-reference warning but not an address?
The status bar may show that a circular reference exists without displaying a cell address, particularly when the loop is on another worksheet. Use Formulas > Error Checking > Circular References in the desktop app to inspect the available list.

Can a circular reference involve more than two cells?
Yes. A formula can depend on a second cell, which depends on a third, and so on until one of them depends back on the first. Trace precedents and inspect formulas along the whole path.

Should I enable iterative calculation to remove the warning?
Only if the circular calculation is intentional and the workbook is designed to use iteration. For an accidental loop, correct the references instead; enabling iteration can make an unintended formula cycle appear to calculate without fixing its logic.

Does deleting the listed formula always fix the problem?
No. Another cell may still contain a return path, and deleting a formula can damage the model. Inspect the dependency chain, make the smallest correction that matches the intended calculation, and check the circular-reference list again.

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.