What's happening
You edited a formula, and Google Sheets immediately flagged it with:
Circular dependency detected. To resolve with iterative calculation, see File > Settings.
That error means one of your formulas is pointing back at itself — directly, or through a chain of other cells. Take =A1+1 sitting inside A1 as the simplest example. Or picture a longer chain: A1 references B1, and B1 references A1. Either way, Sheets can't resolve the value without looping forever.
Google Sheets blocks these by default — most of the time it's just a typo in a range. Running totals and loan calculators are the classic exceptions: they need iterative calculation, where Sheets re-runs the formula a fixed number of times rather than spinning indefinitely.
Step 1 — Find the circular reference
Before touching any settings, figure out whether the loop is intentional or a formula mistake. Jumping straight to iterative calculation is a common way to bury a real bug.
Click the problem cell and trace its references in the formula bar. On a complex sheet, follow the chain manually:
- Click the cell showing the error.
- Read the formula in the formula bar.
- Open each referenced cell and check what it references.
- Keep going — you'll hit the loop within a few hops.
Two patterns show up constantly:
-- Formula cell falls inside its own range
A1: =SUM(A1:A10) ← A1 is part of A1:A10
-- Two cells pointing at each other
B2: =C2 * 1.1
C2: =B2 - 5 ← B2 → C2 → B2
Caught the culprit? Fix the range or reference directly — no settings change needed.
Step 2 — Fix accidental circular references
Nine times out of ten, the loop is just a range that's one row too wide. Here are the patterns to watch for:
Range includes the formula cell itself
-- Problem: A11 is inside the range it's summing
A11: =SUM(A1:A11)
-- Fix: stop the range one row above
A11: =SUM(A1:A10)
Indirect loop through multiple cells
-- Problem
D2: =E2 + F2
F2: =D2 * 0.9 ← loops back to D2
-- Fix: replace the looping reference with a constant input cell
F2: =InputValue * 0.9 ← InputValue holds a plain number, not a formula
INDIRECT or named range pointing back at itself
Using INDIRECT()? Double-check the string you're constructing. Under certain row or column conditions, it can quietly resolve to the cell it's sitting in.
Step 3 — Enable iterative calculation (when intentional)
Occasionally the circular reference is deliberate. Compound interest formulas, running totals that fold into themselves, loan amortization models — these genuinely need to read their own previous result. That's what iterative calculation is for.
- Go to File → Settings (or File → Spreadsheet settings).
- Click the Calculation tab.
- Check Enable iterative calculation.
- Set Max iterations — the default of 50 works for most use cases.
- Set Threshold — how small a change counts as "converged" (default 0.05). Lower values give more precision.
- Click Save settings.
Once enabled, Sheets keeps recalculating up to Max iterations times — or stops early once the result shifts by less than the Threshold, whichever comes first.
One catch: this setting applies to the entire spreadsheet, not just one formula. Turn it on only when you know exactly which formula needs it — and why.
Step 4 — Structure iterative formulas safely
Iterative formulas can spiral if you're not careful. Every sheet load or unrelated edit can trigger another accumulation. A simple guard cell prevents this:
-- Running total that accumulates only on demand
-- A1 (trigger cell): set to TRUE when you want to add, FALSE otherwise
-- B1 (accumulator):
=IF(A1=TRUE, B1 + C1, B1)
-- Only adds C1 to B1 when A1 is TRUE
-- Flip A1 back to FALSE after each accumulation
Using a dedicated trigger cell means the formula fires only when you want it to. No phantom additions every time someone clicks the sheet or an unrelated cell recalculates.
Verification
Run a quick sanity check before calling it done:
- The cell should show a number, not the error banner.
- Click the cell — the formula bar should display your formula cleanly, no warning indicator.
- Edit an unrelated cell to trigger recalculation. The fixed cell should update correctly without the error coming back.
- If iterative calculation is on, watch that the result stabilizes — it shouldn't keep shifting on each F5 or sheet interaction.
Lessons learned
- SUM ranges are the #1 culprit. Always verify your SUM or AVERAGE range stops before the row the formula lives in.
- Iterative calculation is not a band-aid. Turning it on without understanding the loop often buries a real logic error that bites you later.
- The setting is per-spreadsheet, not per-session. Everyone who opens the file shares it — including collaborators who don't know it's on.
- In shared sheets, leave a comment or a dedicated notes cell explaining that iterative calculation is intentionally enabled. Your future teammates will thank you.