Fix a Circular Reference

Excel & Google Sheets

How it works

A circular reference means a formula's answer depends on itself — the spreadsheet can't finish calculating because the result keeps changing the input. The classic case is putting =SUM(A2:A11) in cell A11: the range includes the total's own cell, so the sum feeds itself. The fix is to end the range one row earlier: =SUM(A2:A10). Loops can also be indirect — A1 refers to B1, which refers back to A1 — which is harder to spot by eye. Excel points you straight to the culprit under Formulas > Error Checking > Circular References, and Google Sheets names the offending cell in its error message. Excel's iterative calculation setting can silence the warning, but it's almost never the right fix — it makes the loop "converge" instead of removing it, hiding a structural mistake.

SUM(A2:A10)

The corrected range stops at row 10, so the total in A11 no longer includes itself.

Formulas > Error Checking > Circular References

Excel lists every cell in the loop — click each one to trace and break the chain.

Sheets error message

Google Sheets shows #REF! with "Circular dependency detected" and names the cell — start your trace there.

When to use it

Trace and break the loop whenever the circular warning appears — most often after extending a SUM range over its own total row, or copying formulas into a totals row.

Common mistakes

• ✕Including the total's own cell in the SUM range.

  ✓=SUM(A2:A11) placed in A11 sums itself. Stop the range one row above the formula: =SUM(A2:A10).

• ✕Turning on iterative calculation to make the warning go away.

  ✓Iteration forces the loop to settle on a number, but the structural error remains and results drift silently. Remove the loop instead.

• ✕Only checking the cell that shows the warning.

  ✓In a chain like A1 → B1 → A1, either cell can trigger it. Use Excel's Circular References list to see every link in the loop.