Excel: Find Cells With Circular References and Fix Them

In Excel for Microsoft 365 on Windows, go to Formulas > Error Checking > Circular References, then select a listed cell to jump to it. Excel’s status bar may also show a circular-reference cell address; inspect the formula and its dependencies to find where the loop begins.

What a Circular Reference Means

A circular reference occurs when a formula depends on its own result, either directly or through other cells. Excel cannot calculate a normal one-way result from a loop unless iterative calculation is enabled. A direct circular reference points straight back to the formula cell; an indirect one travels through one or more other cells before returning.

For example, if a formula is entered in B10 and includes B10 in its own sum, the cell refers to itself. With values in B2:B10 and the total intended for B10, the following formula is circular because its range includes B10:

With the total in B10 and values or formulas in B2:B9, this formula includes its own cell: =SUM(B2:B10)

Use a range that stops above the total cell instead. If the values are in B2:B9 and the total is in B10, this formula adds only the inputs: =SUM(B2:B9)

Indirect loops can be harder to spot. Suppose A2 calculates from B2, while B2 calculates from A2. Each formula looks like it refers to another cell, but together they create a cycle. Circular references are not always mistakes—some models intentionally use iteration—but an unexpected one can make results unreliable or prevent expected calculations.

Find the Cell With Excel’s Circular References Menu

The built-in menu is the best first check because it identifies a cell Excel recognizes as part of a circular reference. In desktop Excel for Microsoft 365 on Windows:

Open the workbook and select the worksheet you want to inspect.
Open the Formulas tab.
In the Formula Auditing group, select Error Checking.
Point to Circular References in the menu.
Select a listed cell address to move the selection to that cell.

If the menu is unavailable or has no listed address, Excel may not currently be detecting a circular reference in the open workbook, or iterative calculation may affect how the issue is reported. Check that you are in the intended workbook and that calculation has not been suspended. If the menu lists an address on another sheet, selecting it should take you to that cell.

Excel may expose additional circular references as you fix the first one. Correct the selected formula, recalculate, and check the menu again. A workbook with multiple separate loops may need several passes; finding one address does not guarantee that every circular dependency has been resolved.

Check the Status Bar and Workbook Sheets

When Excel detects a circular reference, the status bar at the bottom of the window can display a message such as Circular References: B10. The address is a useful clue, but it identifies a cell involved in the issue rather than explaining the full chain of formulas. The Circular References menu is a more direct way to select the displayed cell.

If you do not see the status-bar message, use the Formulas menu rather than assuming the workbook is clear. The status bar can be customized, and the message may not give enough detail to locate every loop. Also check other worksheets: a formula on one sheet can refer to a cell on another sheet, and that second cell can eventually point back to the first.

When reviewing a large workbook, note the sheet name and address for each reported cell. A simple checklist helps distinguish a fixed loop from one that remains:

Record each address shown under Circular References.
Inspect and correct one loop at a time.
Recheck the menu after each correction.
Confirm that expected totals and dependent results recalculate correctly.

Trace the Formula Chain

Finding the flagged cell is only the start. Select it and read the formula bar, then identify every referenced cell. Use Formulas > Trace Precedents to draw arrows from cells used by the selected formula. Select a precedent cell and trace its precedents in turn. If the chain eventually returns to the original cell, you have located the loop.

To see what relies on a cell, select it and choose Formulas > Trace Dependents. This can help determine the consequences of changing a formula. The arrows show relationships, not necessarily a complete, easy-to-read map of a complex workbook, so follow them step by step. Use Remove Arrows in the Formula Auditing group to clear the displayed arrows when finished.

For example, assume A2 is meant to calculate from B2 and B2 is meant to calculate from A2. With those formulas entered in the stated cells, the two formulas form an indirect loop:

With A2 intended to use B2: =B2+1

With B2 intended to use A2: =A2*2

Tracing precedents from A2 leads to B2, and tracing from B2 returns to A2. The right correction depends on the worksheet’s purpose: perhaps one cell should contain an independent input, or a reference was intended to point to a different row. Do not simply replace a formula because it is flagged; first establish what the model is supposed to calculate.

The Evaluate Formula command on the Formulas tab can also help inspect a complicated formula one part at a time. It is useful for understanding nested calculations, though it does not automatically repair a dependency loop.

Fix an Accidental Circular Reference

Most accidental loops come from an incorrect range, a shifted cell reference, or a formula copied into a location that its range includes. Compare the formula with the intended inputs and outputs. Pay particular attention to total rows, running balances, and formulas copied down a column: a relative reference can change as it is filled, sometimes bringing the formula back into its own range.

For instance, if a total is meant to add the nine entries above it, place the total in B10 and use B2:B10 only if B10 is not the formula cell—which it is in this layout. The self-inclusive version is circular. With the total in B10 and inputs in B2:B9, use =SUM(B2:B9). The result is the sum of the eight input cells, and the formula does not include itself.

For indirect loops, identify which cell should be an input and which should be calculated. A worksheet may have accidentally replaced a typed value with a formula, or a formula may point to a summary cell that depends on the original value. Correcting the underlying relationship is safer than hiding the warning by turning on iteration.

After editing, press F9 to recalculate if needed and check the Circular References menu again. On a Mac, use fn+F9 on keyboards where the function keys control hardware features; the exact key behavior depends on macOS keyboard settings. Save the workbook after verifying dependent values, and compare important outputs with a known example or source data.

When Iterative Calculation Is Intentional

Some specialized worksheets deliberately use a circular calculation, such as a model where a result is repeatedly refined until it changes by less than a tolerance. In that situation, Excel’s iterative calculation setting allows it to calculate loops rather than treating them only as errors. Enabling iteration is not a general-purpose fix: it changes how circular formulas are calculated and can affect the workbook’s results.

On Windows, the setting is under File > Options > Formulas. In the Calculation options area, select Enable iterative calculation, then review the maximum iteration and maximum change settings. Iteration limits and tolerance influence when Excel stops calculating, so use values appropriate to the model and document the choice for other workbook users.

On Mac, calculation settings are available through Excel > Preferences > Calculation in some releases; newer releases may label Preferences as Settings. The exact presentation can vary by version. In Excel for the web, calculation controls and auditing tools are more limited than in desktop Excel, so open the workbook in the desktop app to inspect or change these options.

If iteration was enabled to suppress an unexpected warning, investigate the formulas before relying on the results. A circular model can return a number while still being logically wrong, particularly if its starting values, limits, or tolerance are unsuitable. Conversely, if the loop is a documented part of a model, the circular-reference notice may be expected rather than evidence of a newly introduced mistake.

Common Reasons a Reference Is Hard to Find

A formula search is not as dependable as the Circular References menu. Searching for an equals sign or a cell address can produce many unrelated matches, may miss indirect links, and does not establish that a match belongs to a cycle. Use search as a supporting method only after checking the flagged cell and its dependencies.

Some workbook structures add complexity. Named ranges can hide the address behind a descriptive name, and formulas involving other sheets can make the route less obvious. Select the formula and inspect its references; if it uses a defined name, review that name in Name Manager from the Formulas tab. Check whether the name refers to a cell that eventually depends on the formula’s own cell.

Also distinguish a circular reference from a formula error such as #REF!. A #REF! error generally means a reference is invalid, often because a referenced cell was deleted. A circular reference is a dependency loop. The repair is different: a broken reference needs a valid target, while a loop needs a corrected dependency or an intentional iterative calculation design.

In shared workbooks, a loop may appear after someone changes a range, pastes formulas, or edits a named range. If the problem started after a recent change, compare the current formula with a previous version or ask the workbook owner what the calculation is intended to do. Avoid deleting cells or rewriting formulas before understanding downstream effects.

Frequently Asked Questions

How do I find all circular references in Excel?
Start at Formulas > Error Checking > Circular References and select each listed address. Fix a loop, then check the menu again because other circular references may become apparent after the first correction. Trace precedents to follow indirect links.

Why does Excel say circular reference when my formula looks correct?
The formula may include its own cell through a range, or one of its referenced cells may eventually depend on it. Check the full dependency chain, including other worksheets and named ranges, rather than looking only at the formula’s direct references.

Can I find a circular reference if Excel iteration is enabled?
Yes, inspect the Circular References menu and trace the formula dependencies. Iterative calculation can allow a loop to calculate, but it does not make the dependency disappear or prove that the model is correct. Review the calculation settings and the intended logic.

Does a circular reference always mean the workbook is wrong?
No. Some models intentionally use iteration. However, an unplanned loop can cause unexpected values or prevent normal calculation, so confirm that the circular design is deliberate and that its iteration limits and tolerance are suitable.

Can Excel for the web show circular references?
Excel for the web has fewer auditing and calculation controls than desktop Excel. If you cannot locate or investigate the reference there, open the file in desktop Excel and use the Formulas tab’s Error Checking and formula-auditing tools.

What to Verify Before You Finish

Finding the reported address does not by itself confirm that a workbook is fixed. Verify that the loop is gone, that the corrected formula uses the intended inputs, and that totals or outputs depending on it still make sense. If iteration is intentional, make sure the setting and calculation assumptions are clear to anyone who will use the workbook. This final review catches both accidental cycles and changes that remove a warning but alter the model’s meaning.

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.