Spreadsheet error
How to fix a circular reference
TL;DR
A circular reference is a formula that depends on itself. Excel warns you and usually returns 0. Fix: find the cycle via Formulas > Error Checking > Circular References and restructure.
What it means
A circular reference occurs when a cell formula refers to its own cell, either directly (=A1+1 in cell A1) or through a chain (A1 → B1 → C1 → A1). Excel can optionally solve these with iterative calculation, but most cases are bugs.
Common causes
- •Accidentally including the formula cell in a SUM range: =SUM(A1:A10) in cell A10.
- •Chained formulas that loop back (invoice totals that include tax that references subtotal).
- •Deliberate recursion without enabling iterative calculation.
How to fix it
1.Find the cycle
- Formulas > Error Checking > Circular References.
- Excel lists every cell in the cycle.
- Click each to jump and inspect.
2.Restructure the formula
- Move the formula out of the referenced range: =SUM(A1:A10) should be in A11, not A10.
- Break cyclical chains — often the math is wrong, not just the layout.
3.Enable iterative calc (only if intentional)
- File > Options > Formulas > Enable iterative calculation.
- Set Max Iterations and Max Change.
- Only do this if you genuinely need fixed-point iteration (rare in business sheets).
Example
Broken
Cell A10: =SUM(A1:A10)FixedCell A11: =SUM(A1:A10)The SUM in A10 included A10 itself — a self-reference. Moving it to A11 makes the range non-circular.
Prevention
- Put totals below or beside the range, never inside.
- Review complex sheets with Formulas > Trace Precedents to spot loops.
Related errors
Let FormulaPad debug it for you
Paste your broken formula and the error message — get a diff and an explanation in seconds.
Debug a formula