Circular reference warning: what it means and how to fix it
A formula refers to its own cell, directly or through other cells.
A circular reference happens when a formula uses its own cell, directly (=D1+D2+D3 in D3) or through other cells (A1 uses B1 and B1 uses A1). The spreadsheet can't finish the calculation, so it warns you. After the warning, Excel may show 0 or the last calculated value.
Common causes, ranked
- 1
A total that includes its own cell
=SUM(A3:F3)typed into F3 adds F3 to itself.Fix: End the range before the formula's cell.
Shows the error
=SUM(A3:F3)Fixed
=SUM(A3:E3) - 2
A formula that points straight at itself
=A1+A2in A1, usually from clicking the wrong cell while building the formula.Fix: Point at the right cell.
- 3
Two or more cells that depend on each other
A1 is
=B1+1and B1 is=A1+1: each needs the other first.Fix: In Excel, Formulas › Error Checking › Circular References lists the cells; Trace Precedents and Trace Dependents show the loop. Break it by moving one calculation elsewhere.
Should you hide it?
There's nothing to hide; fix the loop. Only a few financial or engineering models use circular references on purpose, with iterative calculation turned on (Excel: File › Options › Formulas; Mac: Excel › Preferences › Calculation; Google Sheets: File › Settings › Calculation).
Careful: Turning on iterative calculation to make the warning go away hides the mistake: the cell keeps a value that isn't really calculated. Leave it off unless the loop is deliberate.
Excel vs Google Sheets
Google Sheets has iterative calculation too, under File › Settings › Calculation, where you set how many times a formula with a circular reference can recalculate.
Sources
Practise this in 5 minutes
Fixing Circular reference is taught in Lesson 5: Ranges and SUM (Unit 2, Ranges and functions), on a live sheet with instant feedback.
Open “Ranges and SUM”Need a quick reference? The formula cheat sheet is free to read and print.