Excel Circular Reference Warning: Find It, Fix It, or Allow It on Purpose

Excel pops up “There are one or more circular references where a formula refers to its own cell either directly or indirectly. This can cause them to calculate incorrectly.” Afterwards, affected formulas often show 0, and the status bar at the bottom reads Circular References.

Why: A formula depends, directly or through a chain, on its own result. The classic direct case is =SUM(A1:A10) sitting inside A10 — it’s adding itself. The sneaky case is indirect: A1 refers to B1, B1 refers to C1, and C1 refers back to A1. Excel can’t resolve the loop, so it warns you and returns 0 for those cells.

Fix 1: Jump to the circular cell with Error Checking

Don’t hunt manually — Excel will take you straight there.

1. Go to the Formulas tab.
2. Click the arrow next to Error Checking (in the Formula Auditing group).
3. Point to Circular References. The submenu lists the cell address(es) involved.
4. Click the first address — Excel selects that cell. Look at its formula and remove the self-reference (for example, change =SUM(A1:A10) in A10 to =SUM(A1:A9)).
5. Repeat: reopen Error Checking > Circular References until the submenu is empty and the status bar no longer says Circular References.

Note: the status bar only shows a cell address when the loop is on the active sheet; if it just says “Circular References” with no address, switch sheets and recheck.

Fix 2: Trace the loop visually for indirect references

When the formula looks innocent, the loop is hiding upstream. Use the audit arrows:

1. Select the suspect cell.
2. On the Formulas tab, click Trace Precedents (which cells feed this one) and Trace Dependents (which cells this one feeds).
3. Follow the blue arrows — a circular reference shows as arrows that form a loop back to the start. Double-click an arrow to jump between the linked cells.
4. Break the loop by rewriting one formula in the chain so it no longer points back to the origin. Click Remove Arrows when done.

Worked example: D2 = =C2*1.1, C2 = =B2+D2. D2 depends on C2, and C2 depends on D2 — a loop. Change C2 to reference a fixed input cell instead of D2, and the loop is gone.

Fix 3: Allow it on purpose with iterative calculation

Some models need a controlled loop (interest-on-balance, certain engineering calcs). Only do this if you understand the math, because it hides genuine mistakes.

1. Go to File > Options > Formulas.
2. Under Calculation options, tick Enable iterative calculation.
3. Set Maximum Iterations (how many times Excel recalculates, e.g. 100) and Maximum Change (the smallest change that stops it, e.g. 0.001).
4. Click OK. Excel now resolves the loop by repeating the calculation until the result stabilizes.

On Mac it’s Excel > Preferences > Calculation > Enable iterative calculation.

FAQ

The warning is gone but I still see 0. Iterative calculation may be off while a loop remains — the cell can’t resolve. Either fix the formula (Fix 1/2) or enable iteration (Fix 3) if the loop is intentional.

A quick way to relocate a bad formula? Cut it (Ctrl + X), paste (Ctrl + V) into a cell outside its own range. That breaks an accidental self-reference fast.

Should I just turn on iterative calculation to make the warning stop? No — for most spreadsheets the circular reference is a bug. Enabling iteration only masks it. Reserve it for models that genuinely require feedback loops.

Sources: Microsoft Support — Remove or allow a circular reference in Excel