Fixes / Google Sheets · Checked

Google Sheets #REF! Circular dependency detected: how to fix it

Short answer

Google Sheets shows this when a formula needs its own result to calculate. Either it points at its own cell, or it points at other cells whose formulas point back to it. Sheets stops and shows #REF! so it doesn't loop forever. The most common cause is a total inside the range it adds up, such as =SUM(B2:B10) in B10. Change the range to end one row above, for example =SUM(B2:B9).

What this message means

A circular dependency means a formula is waiting on its own answer. Sheets cannot finish the calculation, so it shows #REF! in the cell and gives the reason "Circular dependency detected" when you hover over the red triangle. The loop can be direct, where the formula refers to its own cell or a range that includes it. It can also be indirect, where cell A depends on B and B depends on A, possibly through other tabs. Only the cell holding the formula shows the error. Other cells that depend on it may then show #REF! too. Sheets only allows loops on purpose if iterative calculation is turned on.

Common causes

CauseHow to tell
The formula's range includes the cell the formula is in (for example, a total at the bottom of the column it sums).Click the #REF! cell and read the formula bar. The cell's own address (for example B10) sits inside a range like B2:B10, or the formula is in column B and refers to B:B.
A whole-column or open-ended reference that includes the formula cell, such as =SUM(A:A), =COUNTIF(C:C,"x") or an ARRAYFORMULA in the same column it reads from.The formula uses a letter-only range (A:A) or an open range (A2:A), and the formula is in that same column.
Two or more formulas depend on each other (indirect loop), possibly across tabs.The formula doesn't mention its own cell, but a cell it references has a formula that points back to it. For example, Sheet1!C2 uses Summary!B5, and Summary!B5 uses Sheet1!C2.
A formula copied or dragged into a position where its relative references now point at itself.The original formula works but copies further along the row or column show #REF!. The references in the copies shifted onto their own row or column.
A range written without a tab name, so it points at the current tab instead of the data tab.The formula is on a report tab, but it uses a plain range like A2:A50 with no 'Data'! prefix, and that range on the current tab includes the formula cell.
An intentional loop (a running timestamp, a goal-seek style model or a self-updating counter) with iterative calculation turned off.You built the formula to reference itself on purpose, for example =IF(A2="","",IF(B2="",NOW(),B2)) in B2, and it shows #REF! instead of a value.

How to fix it

1. Remove the formula's own cell from its range

  1. Click the cell showing #REF! and hover over it to confirm the message says "Circular dependency detected".
  2. Look in the formula bar for a range that contains the cell's own address.
  3. Change the range so it stops before the formula cell. For example, change =SUM(B2:B10) in B10 to =SUM(B2:B9).
  4. If you would rather keep the range, move the formula to a cell outside it (for example, put the total in B12 or in another column).
  5. Press Enter and check that the value appears.

2. Replace whole-column references that include the formula

  1. Find letter-only or open-ended ranges in the formula, such as A:A or A2:A.
  2. If the formula sits in that same column, make the range bounded and stop it above the formula, for example =SUM(A2:A999) with the formula in A1000.
  3. Or keep the whole-column reference and move the formula to a different column or tab, for example =SUM(A:A) placed in D1.
  4. For an ARRAYFORMULA that fills a column, make sure it only reads from other columns, not the column it writes into.

3. Trace and break an indirect loop between cells or tabs

  1. Click the #REF! cell and write down every cell or range its formula references.
  2. Click each of those cells (use the tab name in the reference to find cells on other tabs) and check whether their formulas lead back to the first cell.
  3. Once you find the pair or chain that points back, decide which cell should hold a plain input value instead of a formula.
  4. Replace that formula with a typed value, or rewrite it so it uses the source data directly instead of the result of the other formula.
  5. Recheck the original cell. Other #REF! errors further down the chain should clear at the same time.

4. Add the tab name to references meant for another tab

  1. Open the formula and find ranges that should point at your data tab but have no tab prefix.
  2. Add the tab name, for example change =FILTER(A2:A50,B2:B50>4) to =FILTER(Data!A2:A50,Data!B2:B50>4).
  3. Use single quotes if the tab name has spaces, for example 'Sales 2026'!A2:A50.
  4. Press Enter and confirm the error is gone.

5. Fix references that shifted when the formula was copied

  1. Compare the working original formula with a broken copy in the formula bar.
  2. Find the reference that moved onto the copy's own row or column.
  3. Lock that reference with dollar signs (for example $B$2:$B$9) so it doesn't shift. Click the reference and press F4 on Windows or Fn+F4 / Cmd+T on Mac to cycle the $ options.
  4. Copy the corrected formula back over the broken cells.

6. Allow the loop on purpose with iterative calculation

Google Sheets in a web browser (Windows, Mac, ChromeOS). The setting is saved per spreadsheet.

  1. Only use this if the self-reference is deliberate (timestamps, interest or goal-seek models). Otherwise use the fixes above.
  2. Click File > Settings, then open the Calculation tab.
  3. Set Iterative calculation to On.
  4. Set Max number of iterations (how many calculation rounds Sheets runs) and Threshold (Sheets stops early once results change by less than this). For a timestamp, low values are fine. For a model that has to settle on a value, try 100 iterations and a small threshold such as 0.001.
  5. Click Save settings. The #REF! should be replaced by a value.

Next time, skip the search

Crade is an AI assistant for Mac and Windows that sees your screen. When a message like this pops up, ask “what does this mean?” and it explains the exact error in front of you, step by step.

Download Crade

Still not fixed?

Copy the spreadsheet (File > Make a copy) and in the copy delete formulas one at a time, starting with the ones the #REF! cell references, until the error disappears. The last formula you removed is part of the loop. Check named ranges (Data > Named ranges) as well, because a named range can quietly include the formula cell. If the file is shared, a collaborator may have changed a range, so File > Version history > See version history can show when the error first appeared and what changed. For Google Workspace accounts, your admin or Google Workspace support can help if the file behaves differently for different users.

Questions people ask

How do I find which cell is causing the circular dependency?

Sheets doesn't draw the loop for you. Start at the #REF! cell, open each cell its formula references, and follow those formulas until one leads back to the start. That cell, or the last formula in the chain, is where the loop closes.

Why does IFERROR not hide the circular dependency error?

IFERROR wraps the formula that contains the loop, so the wrapper becomes part of the loop and Sheets still refuses to calculate it. The reference itself has to change.

Is it safe to turn on iterative calculation?

It's safe when the loop is intentional, but it also hides accidental loops. Mistakes then produce wrong numbers instead of a visible #REF! error. Turn it on only in files that need it.

Why does =SUM(A:A) give a circular dependency error?

A:A means the entire column, including the cell holding the formula. Put the formula in another column, or use a bounded range that stops above it.

Can I make a timestamp that doesn't change in Google Sheets?

Yes. A formula like =IF(A2="","",IF(B2="",NOW(),B2)) in B2 keeps the first time A2 was filled, but it only works after you turn on iterative calculation under File > Settings > Calculation. To insert a fixed timestamp by hand, press Ctrl+Shift+; on Windows or Cmd+Shift+; on Mac.

Sources

Related errors