How to Fix "Circular Dependency Detected" in Google Sheets

By Gerard Fernandez · Updated · 3 min read

A circular dependency is a formula that, directly or through other cells, depends on its own result. Sheets can't calculate it, so it shows #REF! with the message "Circular dependency detected".

What the error looks like

In our test sheet, the total in B5 is =SUM(B2:B5). The range includes B5 itself, so the total would have to add itself:

Google Sheets total in B5 showing #REF! with the tooltip Circular dependency detected. To resolve with iterative calculation, see File > Settings, and the formula =SUM(B2:B5)
Real test: "Circular dependency detected. To resolve with iterative calculation, see File > Settings."

Fix: take the cell out of its own range

Change the range so it stops above the formula:

=SUM(B2:B4)
The same total with =SUM(B2:B4) in B5, now showing 1240
With the range ending at B4, the total works: 900 + 300 + 40 = 1240.

The most common versions of this mistake (we tried all three in our test sheet, and each one returned #REF!):

FormulaProblemFix
=SUM(B2:B5) in B5The total is inside the range=SUM(B2:B4)
=SUM(B:B) anywhere in column BA whole-column range always includes the formulaPut the total in another column, or use =SUM(B2:B100)
=C2*2 in B2 and =B2+1 in C2Two cells refer to each otherDecide which one holds the real input and type a value there

Finding a longer loop

When the loop runs through several cells, click the cell with the error and look at its formula. Click each cell it refers to and follow the chain until you get back to the start. The cell where you'd expect a typed value but find a formula is usually the one to change.

When a circular reference is intentional: iterative calculation

Some models are circular on purpose, for example interest that depends on a balance that includes the interest. For those, go to File → Settings, open the Calculation tab, and change Iterative calculation from Off to On. Two more boxes appear: Max number of iterations (50 by default) and Threshold (0.05 by default). Click Save settings. Sheets then repeats the calculation until the result changes by less than the threshold, or until it reaches the maximum.

Google Sheets Settings for this spreadsheet, Calculation tab, with Iterative calculation set to On, Max number of iterations 50 and Threshold 0.05
File → Settings → Calculation, with Iterative calculation switched to On.

Only use this on purpose. With iterative calculation on, accidental circular formulas stop showing an error and quietly give wrong numbers.

Frequently asked questions

Why does it say #REF! and not something clearer?

Google Sheets groups circular references with other reference errors. Hover over the cell to see the real reason. Other causes of #REF! are covered in how to fix #REF!.

Where should I put column totals?

Below the data with a fixed range (=SUM(B2:B4)), or in a separate column or row with an open-ended range (=SUM(B2:B)). See how to sum a column.