How to Find Circular References in Excel

Updated 2026-06-03

Quick answer

To find a circular reference in Excel, go to the Formulas tab → Error Checking ▾ → Circular References. Excel lists the offending cell addresses, and the status bar also shows 'Circular References: [cell]'. For models with many sheets, native Excel only shows one at a time; an audit add-in like Formula Audit XL lists every circular reference across the whole workbook at once.

A circular reference occurs when a formula refers back to its own cell, directly or through a chain of other cells, creating a loop that Excel cannot resolve in a single calculation pass. In a financial model, they can produce #REF errors, silently return zero, or cause iterative calculations to produce wrong results. Finding them is the first step of any model audit.

What is a circular reference?

When cell C5 contains =C5+1, it depends on itself. That is a direct circular reference. Indirect circular references are more common and harder to spot: C5 references D10, which references F3, which references C5. Excel detects both types and warns you via a blue status-bar message and a dialog on file open.

Find it via Formulas → Error Checking

The primary native method:

Click the Formulas tab.
In the Formula Auditing group, click the small arrow (▾) next to Error Checking.
Hover over Circular References. A submenu lists the first cell involved.
Click the cell address to navigate there immediately.

After you fix that circular reference, return to Error Checking → Circular References to check whether another one exists. Excel only surfaces one at a time.

Read the status bar

Without opening any menu, look at the status bar at the very bottom of the Excel window. If a circular reference exists anywhere in the workbook, it reads:

Circular References: B14

This is always visible regardless of which sheet is active, so it is a fast first check when opening an unfamiliar model.

Enable iterative calculation (when the circular reference is intentional)

Some financial models are deliberately circular. A debt schedule where interest depends on the ending balance, or a tax calculation that feeds back into net income, are common cases. To allow this:

Go to File → Options → Formulas.
Under Calculation options, tick Enable iterative calculation.
Set Maximum Iterations (typically 100) and Maximum Change (typically 0.001).

With iterative calculation on, the warning disappears and Excel converges on a solution. If the model does not converge correctly, the logic needs checking; a poorly structured iterative model can produce wrong numbers silently.

Why native Excel misses cross-sheet circular references

The Error Checking submenu only shows one cell at a time. In a multi-sheet model with ten or twenty sheets, working through them sequentially takes time, and it is easy to lose track of which ones you have checked. Additionally, circular references that run through named ranges or across workbooks are harder to follow using the built-in arrows.

Find them all at once with Formula Audit XL

Formula Audit XL scans the entire workbook in one pass and lists every circular reference (direct and indirect, across all sheets) in a single results panel. Each entry shows the affected cell, the chain of dependencies, and a link to jump there directly. On large models, that reduces the diagnostic time from minutes to seconds.

Try the free web tool first: upload your .xlsx to the Excel Circular Reference Finder and get a full report with no installation required.

Common pitfalls

Fixing the wrong cell. Circular references involve at least two cells. If you change only one, the loop may persist. Trace the full chain before editing.
Enabling iterative calculation as a workaround. It is sometimes the right answer, but enabling it globally to silence a warning in a non-iterative model masks a real formula error.
Circular references in hidden sheets. Excel’s status bar shows the address but does not indicate the sheet if you are viewing a different one. Go to Error Checking to find the sheet, not just the cell.
Volatile functions inside a loop. If a circular chain includes NOW(), RAND(), or OFFSET(), the result will recalculate on every edit, making behaviour unpredictable.

Free tool: Excel Circular Reference Finder

The faster way

Run this check across your entire model with Formula Audit XL.

Audit Checks

Frequently asked questions

Why does Excel show a circular reference warning when I open a file?

Excel detects that a formula refers back to its own cell, either directly or through a chain of other formulas. This creates an infinite loop. You may have inherited it from someone else's model. Go to Formulas → Error Checking → Circular References to locate it.

Can a circular reference be intentional?

Yes. Some models use intentional circular references for iterative calculations. A debt schedule where interest expense feeds into the cash balance is a common example. To allow this, enable iterative calculation under File → Options → Formulas and set a maximum iteration count.

Why does Excel only show one circular reference at a time?

By design, Excel reports circular references one at a time. Once you fix the first one it reports, it will surface the next. In a large multi-sheet model this can be slow. A dedicated audit tool reports them all in a single pass.

How do I fix a circular reference?

First, decide if it is intentional. If not, trace the chain using Formulas → Trace Precedents to see which cells form the loop, then break the loop by either changing a formula to reference a non-circular cell or by replacing a formula with a hardcoded value at one point in the chain.

Does Ctrl+Z undo a circular reference fix?

Yes. Excel tracks formula edits in its undo stack. If you accidentally break something while fixing a circular reference, Ctrl+Z will restore the prior formula.

Related guides