#REF! means a formula points to something that doesn’t exist or can’t be used: cells that were deleted, a sheet that isn’t there, a column beyond the end of a range or a result that has no room to appear. The quickest way to find the cause is to read the message: point at the cell with the error and Google Sheets shows a short explanation. Each cause below comes with the exact message we got in Google Sheets.
Tested in Google Sheets. We reproduced every cause on this page in Google Sheets on November 10, 2026 and copied the error message it showed. How we test
Find your cause by the message
| Message in Google Sheets | Cause |
|---|---|
| Reference does not exist. | 1. The referenced cells were deleted |
| Circular dependency detected. | 2. The formula refers to itself |
| VLOOKUP evaluates to an out of bounds range. | 3. A column or row number is too large |
| Unresolved sheet name ‘…’. | 4. The sheet name is wrong or the tab was deleted |
| Function INDIRECT parameter 1 value is ‘…’. It is not a valid cell/range reference. | 5. INDIRECT receives text that isn’t a valid reference |
| Array result was not expanded because it would overwrite data in … | 6. The result has no room to spill |
| A message asking you to connect the sheets | 7. IMPORTRANGE has not been given access |
1. The referenced cells were deleted
If you delete a row, column or sheet that a formula uses, Google Sheets rewrites the reference as #REF! inside the formula itself. In our test, =J2 became =#REF! after we deleted column J, with the message Reference does not exist.
Fix: undo the deletion straight away (Ctrl+Z, or Cmd+Z on a Mac). If it is too late for that, retype the reference to the right cells. Deleting the contents of a cell (pressing Delete) does not cause #REF!: only deleting the cells themselves does.
2. The formula refers to itself (circular reference)
A formula that depends on its own cell, directly or through other cells, can never finish calculating. =B12+1 typed in B12 returned #REF! with Circular dependency detected. To resolve with iterative calculation, see File > Settings.
Fix: change the formula so it doesn’t include its own cell. A common case is a total such as =SUM(B2:B20) placed in B20. Only turn on iterative calculation if the circular reference is intentional.
3. A column or row number is too large
Lookup and reference functions return #REF! when you ask for a position outside the range:
=VLOOKUP(1, {1,2}, 3, FALSE)asks for column 3 of a two-column range: VLOOKUP evaluates to an out of bounds range.=INDEX({"a","b"}, 1, 27)asks for column 27 of a two-column array.=OFFSET(A1, -1, 0)moves above row 1: OFFSET evaluates to an out of bounds range.
Fix: count the columns of the range you pass, starting from its first column, not from column A of the sheet. In =VLOOKUP(A2, C:E, 3, FALSE), the 3 is column E. To work out a column’s number from its letter, use our column letter converter.
4. The sheet name is wrong or the tab was deleted
='No such sheet'!A1 returned Unresolved sheet name ‘No such sheet’. Check the spelling against the tab, including spaces. Sheet names with spaces or symbols must be in straight apostrophes: ='Sales 2026'!A1.
5. INDIRECT receives text that isn’t a valid reference
INDIRECT turns text into a reference, so a typo in the text only shows up when the formula runs. =INDIRECT("NoSuchSheet!A1") returned Function INDIRECT parameter 1 value is ‘NoSuchSheet!A1’. It is not a valid cell/range reference. Build the text in a separate cell first so you can see exactly what INDIRECT receives.
6. The result has no room to spill
Functions that return several cells, such as SEQUENCE, FILTER, QUERY, UNIQUE or ARRAYFORMULA, need empty cells for the whole result. When we put =SEQUENCE(3) above a cell that already held text, it returned Array result was not expanded because it would overwrite data in, followed by the address of the blocking cell.
Fix: clear the cell named in the message, or move the formula to a place with enough empty space. An XLOOKUP that returns several columns spills to the right in the same way; see XLOOKUP returning multiple columns.
7. IMPORTRANGE has not been given access
The first time a spreadsheet imports from another one, someone has to allow the connection. Until then IMPORTRANGE returns #REF!. In our test, an IMPORTRANGE to a second spreadsheet of the same account returned #REF! with a message asking to connect the sheet from a desktop web browser.
Fix: according to Google’s help page, if you own the source spreadsheet, the #REF! shows the prompt You need to connect these sheets with an Allow access button: click it in a desktop browser. If you don’t own the source, open its URL and request access from the owner first. Also check the two arguments: the spreadsheet URL or ID and the range must both be in quotes, for example =IMPORTRANGE("spreadsheet_url", "Sheet1!A1:C10"). For every other IMPORTRANGE message, see IMPORTRANGE not working: every error and fix.
Stop #REF! from appearing in reports
You can hide errors with IFERROR, but think twice: a #REF! usually means the formula is broken, and hiding it can leave wrong totals without any warning. In our tests, IFERROR turned an out-of-range VLOOKUP into a blank cell, while IFNA left the #REF! visible. If you only want to hide “not found” results, use IFNA, as explained in how to replace #N/A with a blank.