Skip to content
Formula Audit XL

How to Find Circular References in Excel

Updated 2026-11-12

Quick answer

To find a circular reference in Excel, open the Formulas tab, click the arrow next to Error Checking, and choose Circular References. Excel lists a cell in the loop; click it to jump there. The status bar also shows 'Circular References' when a loop exists. To fix it, change one formula in the chain so it no longer points back to itself. To list every circular reference in a workbook at once, use the free Excel Circular Reference Finder.

To find a circular reference in Excel, open Formulas, click the arrow next to Error Checking, and choose Circular References. Excel lists a cell in the loop. Click it to jump there. To fix the reference, edit one formula in the chain so it no longer depends on its own result. If you need every circular reference in the workbook at once, open the file in the free Excel Circular Reference Finder.

What is a circular reference in Excel?

A circular reference is a formula that depends on its own result. It comes in two forms:

  • Direct: cell C5 contains =C5+1. The formula refers to its own cell.
  • Indirect: C5 uses D10, D10 uses F3, and F3 uses C5. No single formula looks wrong, but the chain is a loop.

Excel cannot finish the calculation in one pass. It shows a warning when you enter the formula or open the file, and the cell usually displays 0. In a financial model, a loop that returns a plausible number is the dangerous case, because nothing flags it as wrong.

How to find a circular reference in Excel

  1. Click the Formulas tab.
  2. In the Formula Auditing group, click the arrow next to Error Checking.
  3. Point to Circular References. Excel lists a cell address that is part of a loop.
  4. Click the address. Excel selects that cell, on whichever sheet it is.
  5. Look at the formula in the formula bar and note which cells it uses.

If Circular References is greyed out, Excel found no loop in the last calculation.

You can also check the status bar at the bottom of the window. It reads Circular References while a loop exists. When the loop is on the active sheet, it adds the cell address, for example Circular References: B14. When the loop is on another sheet, the address is missing, so use the Error Checking menu to find the sheet.

Trace the loop with arrows

A single flagged cell does not show the whole loop. With the flagged cell selected:

  1. Press Alt+M, P (Trace Precedents) to draw arrows from its inputs.
  2. Press it again to go one level further back.
  3. Follow any dotted arrow to another sheet by double-clicking it.
  4. Keep going until an arrow leads back to the cell you started from. That is the loop.
  5. Clear the arrows with Alt+M, A, A (Remove Arrows).

The Trace Precedents guide covers multi-level tracing and cross-sheet arrows. The Trace Dependents guide covers the forward direction. Both are useful here, because a loop runs in both directions.

How to remove or fix a circular reference in Excel

Decide first whether the loop is intended.

If it is a mistake, break the chain at one point:

  1. Pick the formula in the loop that should not depend on the others. This is often a total that was pointed at a range including itself, such as =SUM(B2:B10) entered in B10.
  2. Edit it so it references the correct range or a separate input cell.
  3. Press Enter, then recheck Formulas, Error Checking, Circular References.
  4. Repeat until the item is greyed out.

Replacing one formula with a typed value also breaks the loop, but that hides the logic. Use it only as a temporary test. Press Ctrl+Z if an edit goes wrong.

If it is intended, turn on iterative calculation, described next.

How to enable iterative calculation in Excel

Some models are circular on purpose. Interest calculated on the average debt balance is the usual example, because interest changes cash flow, which changes the closing balance, which changes interest.

  1. Go to File, Options, Formulas. On Excel for Mac, go to Excel, Preferences, Calculation.
  2. Under Calculation options, tick Enable iterative calculation.
  3. Set Maximum Iterations (default 100) and Maximum Change (default 0.001).
  4. Click OK.

The warning stops, and Excel repeats the calculation until the change between passes is below the maximum change. Two cautions:

  • Excel takes this setting from the first workbook opened in a session. A file saved with iteration on can still warn on a machine where another workbook opened first.
  • Iteration on a model that was never meant to be circular hides a real error. Turn it on only after you have confirmed the loop is deliberate.

For the Mac steps, the defaults and when a circular model is safe, see How to Enable Iterative Calculation in Excel.

What native Excel does not do

  • The Error Checking menu shows the cells from the last calculation. It does not draw the whole loop, and it does not give you one list of every loop.
  • Fixing one loop can reveal the next, so you recheck after every edit.
  • Tracing a loop that crosses many sheets means double-clicking dotted arrows to move between tabs.
  • A loop through a defined name is hard to follow with arrows.

Find every circular reference at once

The free Excel Circular Reference Finder reads an .xlsx, .xlsm or .xls file in your browser and lists the circular references it finds across all sheets. The file is not uploaded to a server.

Formula Audit XL runs the same check inside Excel. The Audit Checks suite includes a Circular References check (Ctrl+Shift+C) that lists every loop in the workbook with the cells involved, and a click jumps to each one. To inspect a flagged cell, Explore Precedents and Dependents opens a drill-down tree for it. Formula Audit XL is available as an Excel add-in for Windows and as an AppSource task pane for Windows, Mac, and Excel on the web.

Common pitfalls

  • Fixing the wrong cell. A loop involves at least two cells. If you change one formula and the warning stays, trace the whole chain before the next edit.
  • Turning on iteration to silence a warning. It is the right answer for a deliberate loop. In any other model it hides a formula error.
  • Loops on hidden sheets. The status bar gives no address when the loop is on another sheet. Use the Error Checking menu to see which sheet it is on.
  • Volatile functions in a loop. If the chain includes NOW(), RAND(), or OFFSET(), the result recalculates on every edit and changes without warning.

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

What is a circular reference in Excel?

A circular reference is a formula that depends on its own result, either directly (=A1+1 typed in A1) or through a chain of other cells (A1 uses B1, B1 uses C1, C1 uses A1). Excel cannot finish the calculation in one pass, so it shows a warning and usually returns 0.

How do I find a circular reference in Excel?

Go to Formulas, click the arrow next to Error Checking, and point to Circular References. Excel lists a cell in the loop. Click the address to go to it. The status bar also reads 'Circular References' while a loop exists, and shows a cell address when the loop is on the active sheet.

How do I remove a circular reference in Excel?

Select a cell in the loop, find the formula that points back to the chain, and edit it so it references a different cell or a fixed input. Then recheck Formulas, Error Checking, Circular References. When the menu item is greyed out, no circular reference is left.

How do I fix a circular reference in Excel?

First decide whether the loop is intended. If it is not, use Trace Precedents (Alt+M, P) from the flagged cell to see the chain, then break it at one point: point the formula at a non-circular input, or replace the formula with a value. Use Ctrl+Z if the edit makes things worse.

How do I enable iterative calculation in Excel?

Go to File, Options, Formulas, and tick Enable iterative calculation under Calculation options. Excel defaults to 100 maximum iterations and a maximum change of 0.001. In Excel for Mac, the setting is under Excel, Preferences, Calculation. Enable it only when the loop is deliberate, such as interest on an average debt balance.

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

The workbook contains a loop, and iterative calculation is off in the session that opened it. You may have inherited the loop from someone else's model. Excel applies the iteration setting from the first workbook opened in the session, so the same file can warn on one machine and not on another.

Why does Excel show only some circular references?

The Error Checking menu lists the cells Excel found during the last calculation, and the list changes as you fix loops. In a multi-sheet model, recheck the menu after each fix. A workbook-wide scan lists every loop in one pass.

Audit your whole model in minutes

Formula Audit XL runs this check and the full audit across every sheet at once.

For Microsoft Excel on Windows, Mac and the web · No data leaves your machine