How to find and fix circular references in Excel

A circular reference is one of the most common, and most quietly dangerous, problems in an Excel workbook. It happens when a formula depends on its own result, so Excel has no stable value to land on. Sometimes it’s deliberate. Far more often it’s a mistake that silently throws off a total, and you don’t notice until someone downstream does.

The fastest way to find one: on the Formulas tab, use Error Checking, then Circular References, and Excel points you to the offending cells one at a time. You fix a circular reference by changing the formula so it no longer includes its own cell, or by turning on iterative calculation if the loop is deliberate. To list every loop at once, scan the workbook’s dependency graph instead of relying on the status bar.

What is a circular reference?

A circular reference occurs when a formula refers back to its own cell, either directly or indirectly.

The classic direct example: you type =SUM(A3:F3) into cell F3. The range A3:F3 includes F3 itself, so the formula is trying to add its own result to its inputs. Excel can’t resolve that, so it warns you, and the cell shows a zero or the last value it managed to calculate.

An indirect circular reference is the same trap spread across cells: A1 refers to B1, B1 refers to C1, and C1 refers back to A1. No single formula points at itself, but together they form a loop.

The first time Excel finds a circular reference, it warns you that a formula refers to its own cell, directly or indirectly. Click OK or close the message and you’re back in the sheet.

Here’s the part that matters: the looping cell now displays either a zero or the last calculated value. That’s exactly why circular references are easy to miss: there’s no red error text like #REF!, so the model just quietly returns the wrong number.

How to find a circular reference in Excel

Excel has a built-in finder. On the Formulas tab, select the arrow next to Error Checking, then point to Circular References. The submenu lists the address of an offending cell; select it to jump straight there.

You can also watch the status bar in the lower-left corner of the Excel window. When a circular reference exists, it reads “Circular References” followed by a cell address.

The status bar has a limit, though: it shows the address of only one circular reference at a time. In a small sheet that’s fine. In a real financial model with dozens of tabs, you fix the one Excel names, recalculate, and the next loop appears, so finding them all becomes a slow game of whack-a-mole. More on the faster way at the end of this page.

A couple of things make Excel’s finder miss loops in practice:

• The circular reference can sit on a different worksheet than the one you’re looking at.
• If iterative calculation is switched on (see below), circular references are something Excel has been told to allow, so don’t count on the warning or the status bar to surface a new one.

How to fix a circular reference

Once you’ve found the cell, there are two reliable fixes.

1. Correct the reference so it no longer includes its own cell

This is the real fix most of the time. In the =SUM(A3:F3) example in cell F3, the problem is the range itself:

Change it to end at E3, so the total sums only the input cells, not itself:

2. Move the formula outside the range it references

If the formula belongs where it is conceptually but happens to fall inside its own input range, cut and paste it somewhere else: press Ctrl+X to cut, select a cell outside the range, and press Ctrl+V to paste.

To untangle an indirect loop, use Formulas → Trace Precedents (and Trace Dependents) to draw arrows between the cells a formula feeds from and into. Follow the arrows around until you find the link that closes the circle, then break that one.

After each fix, recalculate and check the status bar again. Keep going until it no longer reads “Circular References”. Because of the one-at-a-time behavior, the workbook isn’t clean until that indicator is gone for good.

When a circular reference is intentional: iterative calculation

Not every circular reference is a bug. Some models are designed to loop. An interest calculation, for instance, can depend on a balance that itself depends on the interest. These rely on Excel recalculating over and over until the numbers settle, which is called iterative calculation.

To turn it on:

• Windows: File → Options → Formulas, and under Calculation options select Enable iterative calculation.
• Mac: the Excel menu → Preferences → Calculation.

Two settings control how far Excel iterates:

• Maximum Iterations: how many times the formulas recalculate (default 100).
• Maximum Change: the smallest change between passes worth continuing for (default 0.001).

Excel stops at whichever comes first: 100 iterations, or all values in the loop changing by less than 0.001 between passes.

Use it sparingly. Iterative calculation is a workbook-wide switch: it tells Excel that circular references are allowed everywhere in the file, so a genuine mistake elsewhere is no longer something Excel treats as a problem. Only enable it when you’re deliberately building an iterative model, and write down that you did.

Find every circular reference at once

Excel’s one-at-a-time finder is the bottleneck when you’re reviewing a model you didn’t build: an audit, a handover, a change someone else made. You want every loop listed up front, in a single pass.

That’s what SheetDelta’s free risk scan does. It reads the workbook’s formulas, builds a dependency graph, and reports every cycle in one pass, including self-references and multi-cell loops. For each cycle it shows the exact path the dependency takes, closed into a loop.

Here it is on a small test workbook containing both kinds of loop from this article: the direct =SUM(A3:F3) typed into F3, and an indirect chain where A5 reads B5, B5 reads C5, and C5 reads back to A5. Output from the command-line sheetdelta audit, trimmed to the findings:

Both loops, one pass, each with its full path. The free in-browser scan runs the same check. Two details matter for trusting the result:

• It’s static. SheetDelta parses the formula text and never opens Excel or recalculates the workbook. That makes the scan fast, and safe to run on a file you don’t fully trust.
• It catches a related silent trap: a formula whose input cell is empty, so the calculation quietly proceeds on a zero. Like a circular reference, that produces a wrong number with no visible error.

Why circular references are worth taking seriously

Because a broken loop usually shows up as a plain 0 rather than an error, it’s the kind of mistake that survives review and reaches a decision. Spreadsheet errors of exactly this quiet, formula-level kind are well documented: the European Spreadsheet Risks Interest Group (EuSpRiG) keeps a long public record of real cases where a single bad formula led to a material miscount.

The fix is rarely the hard part. Finding every loop before the workbook goes out the door is. Excel gets you there one cell at a time; a checker that reads the whole dependency graph gets you there in one.