Excel: "There are one or more circular references"

Hi, it's BlueByte. A circular reference warning sounds vague, but the fix is concrete: find the one cell that closes the loop and break it. Let's have Excel point you straight at that cell, fix it, and cover the one case where the loop is actually on purpose.

What the warning means

A warning appears and a cell shows 0 or a wrong result:

There are one or more circular references where a formula refers to its own
cell either directly or indirectly.

Excel cannot compute a formula that depends on its own output, so it stops, warns you, and leaves the affected cells at 0 until you break the loop. The trick is finding exactly which cell closes the loop, because the warning names the workbook, not always the cell.

How a formula ends up referring to itself

A cell's formula includes its own cell in the calculation, one of two ways:

• Directly — A1 contains =A1+1, referring to itself.
• Indirectly — A1 uses B1, and B1 uses A1, so the two depend on each other through a chain, sometimes several cells long.

Each recalculation would change an input to itself, so there is no stable answer. Occasionally the loop is deliberate (some engineering or financial models iterate on purpose), but usually it is an accident from dragging a formula one cell too far.

Let Excel point you at the exact cell

You do not have to hunt for it. On the Formulas tab, open the Error Checking dropdown and point to Circular References — it lists the offending cell; click to jump there. The status bar also shows "Circular References:" with a cell address. Then use Trace Precedents (same tab) to see which cells feed the formula and where the loop closes — following the arrows back to the starting cell shows you the whole chain.

Break the loop (or allow it, if intended)

1. Go to the cell the Circular References list points at.

2. Rewrite the formula so it no longer depends on its own result — usually by pointing at a different input cell, or by splitting the calculation across two cells so nothing feeds itself.

3. If the loop is intentional, enable it deliberately: File → Options → Formulas → Enable iterative calculation, and set a sensible maximum iteration count and change threshold.

A real case: a SUM that summed itself

A running-total column suddenly shows 0 and the status bar reads "Circular References: D10". You go to D10 and see =SUM(D2:D10) — the total accidentally includes its own cell because the range was dragged one row too far. You change it to =SUM(D2:D9), the loop is gone, the warning clears, and the running total computes. Trace Precedents on D10 confirmed the loop pointed straight back at D10, which made the fix obvious.

Confirm the warning is gone

Recalculate (F9) and confirm the warning is gone and the cell shows the value you expect. Check the status bar — it should no longer read "Circular References". If a number still looks wrong, run Error Checking again in case a second loop exists on another sheet.

Keep it from recurring

Leave iterative calculation off unless a model genuinely needs it — with it on, Excel stops warning about circular references entirely, which hides accidental loops. Build formulas that read from inputs and write to separate outputs, never back into their own inputs, and double-check ranges after dragging a formula.

How this differs from #REF! and #VALUE!

A circular reference is a structural problem — the formula points at itself. That is different from #REF! (an invalid reference to a deleted cell) or #VALUE! (a wrong argument type): those are about what a formula references, not about it referencing itself. So when you see the circular-reference warning, look at the cell's own range first.