formulastreak

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. 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. 2

    A formula that points straight at itself

    =A1+A2 in A1, usually from clicking the wrong cell while building the formula.

    Fix: Point at the right cell.

  3. 3

    Two or more cells that depend on each other

    A1 is =B1+1 and 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”

Get the formulastreak newsletter

Bite-sized formula tips you can use at work the next day, plus a heads-up when new free tools and lessons land. It arrives now and then, never in a flood.

No spam, unsubscribe in one click. We store your email, this consent and when you gave it. Details in our privacy policy.

Need a quick reference? The formula cheat sheet is free to read and print.