Fixing a circular reference in Excel: how it works
A circular reference is a formula that refers to its own cell, such as =SUM(B2:B6) in B6. Excel warns you and can't work out the result. Take the cell itself out of the formula.
Try the first exercises for freeIn the lesson Understanding error messages
What is a circular reference?
A circular reference is a formula that refers to its own cell, directly or in a roundabout way. If you type =SUM(B2:B6) in B6, Excel has to count B6 to calculate B6. That's a circle without an end: the result depends on itself.
Excel then shows a warning and puts Circular References in the status bar at the bottom, with the cell. The cell itself usually shows 0 or the last result, and that isn't right. It's not an error message with a hash sign, like #REF!, but it is a mistake you always have to fix.
When does a circular reference happen?
- You put a total under a column and include the total cell in the range: =SUM(B2:B6) in B6.
- When creating a formula, you drag the range just one row too far.
- Two cells refer to each other: A1 contains =B1*2 and B1 contains =A1+5.
- You put a share of the total in the same column as the numbers the total adds up.
Fixing a circular reference, step by step
B6 contains =SUM(B2:B5). The range stops above the total cell, at B5. That way, B6 no longer counts itself. Change the numbers or the formula and see what happens.
This is how to go about it:
- Click OK in the warning and look at the status bar at the bottom: it shows Circular References with the cell, such as B6.
- Or choose Formulas > Error Checking > Circular References: Excel shows the cells in the circle, and you jump to them with a click.
- Click the cell and read the formula. Is its own cell in the range, such as B6 in =SUM(B2:B6)?
- Make the range stop above the total cell and press Enter.
=SUM(B2:B5)
How to avoid a circle
The total above the list
B1 contains =SUM(B3:B5). If the total is at the top, start the range below the total cell. =SUM(B1:B5) would count B1 itself.
A share in a column of its own
C2 contains =B2/$B$5. The share is in a column of its own. If you put it in column B, inside the range of the total, the total would depend on the share and the share on the total: a circle.
Where it often goes wrong
- A circular reference can also take a detour: A1 refers to B1, and B1 refers back to A1.
- Don't just dismiss the warning: the cell then shows 0 or an old result, and that's wrong.
Practise this in the lesson Understanding error messages. The first exercises are free.
Frequently asked questions
Is a circular reference ever intended?
Very rarely, in complex financial models. For that you have to turn on iterative calculation in the Excel options. In an ordinary sheet, it's always a mistake.