Excel errors / Circular reference

Circular reference in Excel

A formula depends on its own result.

A circular reference is a formula that refers to its own cell, directly or through other cells. To work out the answer, Excel would first need the answer.

Excel does not show an error value for it. It shows a warning when you enter the formula, puts 0 in the cell, and writes "Circular References" with a cell address in the status bar at the bottom of the window. The sheets on this site show #CIRC! in the cell, so the problem is easier to see.

Have a formula that shows an error? Paste it into the formula checker with a few rows of your data. It points at the part that fails and says why.

3 causes of a circular reference, and how to fix each

  1. A total that includes its own cell

    The total is in A4, and its range runs down to A4. The sum would have to include itself.

    A
    1Sales
    210
    320
    4#CIRC!

    In A4: =SUM(A2:A4) → #CIRC!

    How to fix it. End the range on the row above the total.

    A
    1Sales
    210
    320
    430

    In A4: =SUM(A2:A3) → 30

  2. Two cells that depend on each other

    The tax in B2 is worked out from the total in C2, and the total adds the tax in B2. Neither can be calculated first.

    ABC
    1PriceTaxTotal
    2100=C2*0.2#CIRC!

    In C2: =A2+B2 → #CIRC!

    How to fix it. Work out each value from the ones before it. Here the tax should come from the price.

    ABC
    1PriceTaxTotal
    2100=A2*0.2120

    In C2: =A2+B2 → 120

  3. A whole-column range that contains the formula

    =SUM(A:A) typed in column A includes its own cell. The same goes for =COUNTA(A:A) and other whole-column formulas.

    How to fix it. Put the formula in another column, or give the range a start and an end.

When a loop is meant

A few models need a loop on purpose, such as interest calculated on a balance that includes the interest. For those, turn on File > Options > Formulas > Enable iterative calculation, and Excel repeats the calculation a set number of times. Leave it off otherwise: it hides circular references made by mistake.

Common questions

How do I find a circular reference in Excel?

Go to Formulas > Error Checking > Circular References. The menu lists a cell involved in each loop; click one to jump to it. The status bar at the bottom of the window shows the same address.

How do I remove a circular reference?

Open the cell Excel points to and look for its own address in the formula, on its own or inside a range. Change the reference so that it stops before the cell. If the formula does not mention its own cell, use Formulas > Trace Precedents to follow the chain to the cell that leads back.

Practise it

Functions involved

Other errors