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
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 1 Sales 2 10 3 20 4 #CIRC! In A4:
=SUM(A2:A4)→ #CIRC!How to fix it. End the range on the row above the total.
A 1 Sales 2 10 3 20 4 30 In A4:
=SUM(A2:A3)→ 30Two 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.
A B C 1 Price Tax Total 2 100 =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.
A B C 1 Price Tax Total 2 100 =A2*0.2 120 In C2:
=A2+B2→ 120A 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
- SUM: Adds up numbers, cells and ranges.
- COUNTA: Counts cells that are not empty.
- IF: Returns one value if a condition is TRUE and another if it is FALSE.
Other errors
- #N/A: A lookup could not find the value it was given.
- #VALUE!: The formula was given the wrong kind of value, such as text where it needs a number.
- #REF!: The formula refers to a cell that no longer exists.
- #DIV/0!: The formula divides by zero or by an empty cell.
- #NAME?: Excel does not recognise a word in the formula.
- #NUM!: The calculation has no valid number as its answer.
- #SPILL!: A formula that returns several values has no room to put them.
- #CALC!: A formula that returns a list ended up with nothing to return.
- #NULL!: Two ranges that were expected to cross do not.
- #####: The column is too narrow to show the number, or a date is negative.