How to Fix a Circular Dependency in Google Sheets

A circular dependency means a formula depends on its own result, either directly or through another formula. Move the formula outside the range it reads, or split the calculation into separate input and output columns.

Last verified: July 28, 2026.

What Counts as Circular?#

If a formula in B1 reads B1:B10, Sheets cannot calculate it: the output cell is part of its own input range. The same problem can be indirect—B1 reads C1, while C1 reads B1.

Common examples include:

• =SORT(B1:B10) placed inside column B
• =SUM(A:A) entered in column A
• An IMPORTRANGE or QUERY range that includes the destination cell
• A row formula that refers to a result column in the same row

Find the Loop#

1. Click the cell with the circular dependency warning.
2. Read its formula and mark every referenced range.
3. Check whether the formula cell sits inside one of those ranges.
4. Follow references to other formula cells until you find the first cell that points back.

For a large sheet, temporarily copy the formula to a blank test tab and reduce each range until the warning disappears. That identifies the overlapping reference without changing production data.

Move the Output Outside the Input Range#

Put source data on one tab and the result on another, then use a sheet-qualified range:

=QUERY('Source'!A:N, "select * where Col2 is not null", 1)

For data on the same tab, reserve an output area to the right or below the source range. Array formulas need an empty output range, and that range must not overlap their inputs.

Split Dependent Calculations#

When a row calculation needs the previous row's result, do not force one array formula to write and read the same column. Use a helper column for the intermediate value, then calculate the final display column from that helper. This makes the dependency one-way and easier to audit.

Do Not Use Iterative Calculation as a General Fix#

File > Settings > Calculation > Iterative calculation is intended for deliberate models that converge after repeated passes. Turning it on does not repair an accidental self-reference and can hide a broken formula behind unstable results. Fix the range overlap first.

Quick Checklist#

• Identify the warning cell and inspect its input range.
• Make sure the output cell is outside every range it reads.
• Qualify same-file references with the correct sheet name.
• Move intermediate calculations to helper columns or a separate tab.
• Use iterative calculation only when the circular model is intentional and tested.

Related Google Workspace Fixes#

• How to Fix “Array Result Was Not Expanded” in Google Sheets
• Why Google Sheets Shows the Wrong Date Format

Get the Add-On#

免费获取插件

Text To Table Converter

Work faster inside Google Forms, Docs, Sheets, and Slides: Install the free Text To Table Converter add-on for quiz building, choice syncing, QR codes, document tools, and more.

免费安装