The Circular references guide
A circular reference is a formula that needs its own result, either directly, like =SUM(B2:B7) written in B7, or through other cells. Excel warns once, shows 0, and names one cell of the loop in the status bar: Circular References: B7.
What you see
When you enter the formula, Excel shows a warning that there are circular references. After you click OK, the cells in the loop show 0 (or the last value they had), and the status bar at the bottom says Circular References with one cell address. There's no error code, which makes the 0 easy to believe.
Two kinds of loop
- Direct: the total includes its own cell.
=SUM(B2:B7)in B7 adds B7. End the range one row higher:=SUM(B2:B6). - Through other cells: B8 works out a service charge from the total in B9, and B9 adds B8. Each waits for the other. Charge on the subtotal instead:
=B7*$E$1.
How to find it
- Read the address in the status bar and click that cell.
- Read its formula and the cells it uses. Follow them until one points back.
- In Excel, Formulas → Error Checking → Circular References lists every cell in loops. Fix one loop and the status bar names the next.
Don't switch on iterative calculation
File → Options → Formulas → Enable iterative calculation makes the warning go away by recalculating the loop up to 100 times. That's for models built to loop on purpose. For a total that includes itself, it gives a number that changes every time the sheet recalculates.
Common mistakes
- A total written to the bottom of the column. Selecting down to the total cell with the mouse or AutoSum on a range that includes it.
- Adding the items again in the grand total.
=SUM(B2:B8)in B9 counts the items twice: once on their own and once in the subtotal.
Other errors: #DIV/0!, #REF!. The plain SUM lesson covers ranges.
FAQ
- What is a circular reference in Excel?
- A formula that refers to its own cell, directly or through other formulas, so it needs its own result to work itself out.
- How do I find a circular reference in Excel?
- Look at the status bar, which names one cell of the loop, or use Formulas > Error Checking > Circular References. Click the cell and follow the cells its formula uses.
- Why does my total show 0?
- If the status bar says Circular References, the total probably includes its own cell, like =SUM(B2:B7) in B7. End the range above the total.
- Should I enable iterative calculation?
- Not to fix an accidental loop. It hides the warning and can give a changing result. It is meant for models that are built to loop on purpose.



































































