How to Fix the #REF! Error in Google Sheets

By Gerard Fernandez · Updated · 3 min read

#REF! is a reference error: the formula points somewhere it can't use. In Google Sheets it has three common causes, and the tooltip tells you which one you're dealing with.

Cause 1: "Reference does not exist"

This happens when you delete a row, column or sheet that a formula was using. In our test, H2 added three prices with =C2+C3+C4. After deleting row 3 (Monitor arm), the formula became =C2+#REF!+C3: the deleted cell is replaced by #REF! inside the formula itself.

Google Sheets cell H2 showing #REF! with the tooltip Error: Reference does not exist, and the formula bar =C2+#REF!+C3
Real test: row 3 was deleted, so one of the three references no longer exists.

Fix: press Ctrl + Z straight away to bring the row back. If it's too late, edit the formula and remove or replace the #REF! part.

Prevent it: use ranges instead of adding single cells. In the same test, the total below the prices was =SUM(C2:C5). Deleting the row didn't break it: Sheets shrank it to =SUM(C2:C4) and it showed the new total, 63.

Cause 2: "Array result was not expanded"

Functions like UNIQUE, FILTER, SPLIT, SORT and QUERY fill several cells. If any of those cells already contains something, Sheets refuses to overwrite it:

UNIQUE formula in J2 showing #REF! with the tooltip Array result was not expanded because it would overwrite data in J3, and the word note in J3
Real test: =UNIQUE(B2:B4) in J2 needs J3, which contains "note".

Fix: clear the cell mentioned in the message (here J3). As soon as we deleted "note", the list filled in normally, or move the formula somewhere with enough empty space below and to the right. A cell that looks empty might hold a space; select it and press Delete. More cases, including ARRAYFORMULA, in how to fix "Array result was not expanded".

Cause 3: circular dependency

A formula that includes its own cell, such as =SUM(B2:B5) typed in B5, also shows #REF!, with the message "Circular dependency detected". See how to fix a circular dependency.

Other places you'll see #REF!

  • IMPORTRANGE shows #REF! until you allow access to the other file. See how to fix "You need to connect these sheets".
  • VLOOKUP with a column number larger than the range. In our test, =VLOOKUP("P-101", A2:C4, 4, FALSE) on a 3-column range showed #REF! with "VLOOKUP evaluates to an out of bounds range". Use a column number between 1 and the width of the range.

Not every "out of range" is #REF!, though. =INDEX(A2:A4, 10), asking for row 10 of a 3-row range, showed #NUM! in our test, with "Function INDEX parameter 2 value 10 is out of range". See how to fix #NUM!.

Message says "Unresolved sheet name"? That #REF! means a formula points to a tab that doesn't exist, usually a typo in the tab name. See how to fix "Unresolved sheet name".

Frequently asked questions

Can IFERROR hide #REF!?

Yes, but a #REF! from a deleted cell means the formula is permanently broken. Fix the formula rather than hiding it.

How do I find every #REF! in a sheet?

Use Find and replace, tick Also search within formulas, and search for #REF!. That also finds the broken references inside formulas.