How to Fix the #REF! Error in Google Sheets (Every Cause, Every Fix)

You deleted one column to tidy up a client tracker, pressed Enter, and half the sheet lit up with #REF!. The totals, the lookups, the averages — everything that touched that column is now the same five characters. Fixing the #REF! error in Google Sheets is fast once you know what it’s telling you, because it always means one thing: a formula is pointing at a cell that no longer exists.

Your data isn’t gone. The reference is broken, not the values. This guide covers each cause in the order you’re likely to hit them, with the exact fix for each.

What #REF! Actually Means

Every formula in Sheets resolves to a cell or a range. When the thing it points to is deleted, moved out of reach, or never existed, Sheets has nothing to calculate from and prints #REF! — short for “invalid reference.” It’s distinct from its siblings: #NAME? means Sheets doesn’t recognize a function or named range, and #VALUE! means the math got the wrong type of input. #REF! specifically means the address itself is dead.

How to see exactly which reference broke

1. Click the cell showing #REF!.
2. Look at the formula bar. The #REF! sits exactly where the lost range used to be — for example =SUM(A2:A10)+#REF! tells you a second range was deleted.
3. Double-click the cell (or press F2) to enter edit mode. Sheets color-codes every range the formula still references, so you can see which ones survived and confirm which spot is empty.

That one step — reading the formula bar — tells you which of the five causes below you’re dealing with.

The Five Causes and Their Fixes

1. You deleted a column, row, or sheet a formula needed

This is the most common trigger, and it’s why the error appears right after a cleanup. If a formula used =B2+C2 and you delete column C, the formula becomes =B2+#REF!. Deleting an entire sheet does the same to cross-sheet references: =Summary!A1 collapses to =#REF!A1.

The fastest fix is undo. Press Ctrl + Z (or Cmd + Z on Mac) immediately after the delete and the references come back intact.

If you’ve already made other edits and can’t undo that far, use version history: click File → Version history → See version history, pick a snapshot from before the delete, and either restore it or copy the broken formulas out and repair them by hand. To repair manually, click the cell and type the correct range back in place of the #REF!.

2. A VLOOKUP column index points past the end of the range

The lookup form =VLOOKUP(search_key, range, index, FALSE) counts columns inside range starting at 1. If index is larger than the number of columns in that range, Sheets returns #REF!.

Example: your range is A2:B20 — two columns — but the formula asks for index 3. There is no third column to pull from, so it errors.

1. Click the lookup cell and highlight the range argument in the formula bar.
2. Count its columns. A2:B20 is 2, A2:D20 is 4.
3. Set the index to a number at or below that count.

A sneaky variant: you built the VLOOKUP correctly, then deleted a column inside the range later. The range shrank but the index stayed the same, silently pushing it out of bounds. If a lookup that used to work suddenly shows #REF!, check whether the range lost a column recently.

3. A copied formula now points off the edge of the sheet

Relative references shift when you copy a formula. Copy one far enough up or left and it tries to point above row 1 or to the left of column A — cells that don’t exist.

Concrete case: cell C1 contains =A1+B1. Paste it into A1 and the references shift two columns left, landing before column A — instant #REF!.

Fix it by anchoring the references that shouldn’t move. Add dollar signs: =$A$1+B1 locks column and row; =A$1 locks just the row. Press F4 while the cursor is on a reference to cycle through the lock combinations. If you’re dragging a formula across a whole table, decide first which references are anchors and lock those before you fill.

4. INDIRECT is building a reference that doesn’t exist

=INDIRECT(“Sheet2!A1”) constructs its target from text, so a renamed or deleted sheet breaks it the moment the text stops matching reality. Because the reference is a string, Sheets won’t warn you when you type it — it only fails at calculation time.

Check the text inside the INDIRECT() against your actual sheet names and ranges. If sheet names are dynamic, misspellings and trailing spaces are the usual culprits; wrap the source in =TRIM() if the name comes from another cell.

5. It appeared right after converting an Excel file

Named ranges, structured table references, and links to other workbooks don’t always survive the trip from .xlsx to Sheets. They frequently land as #REF!. If a freshly converted file shows the error in formulas you never touched, this is the cause. Re-point the named ranges under Data → Named ranges, and replace external-workbook links with imported data. Our guide on converting Excel files to Google Sheets covers exactly which pieces break in translation and how to recover them.

Preventing #REF! Before It Happens

A few habits eliminate most of these errors before they occur:

• Hide, don’t delete. Trimming a view? Hide the columns. Deleting is permanent for any formula watching those cells.
• Lock your anchors. Use absolute references ($) for cells a formula should always point to, before you copy or drag.
• Wrap risky lookups. =IFERROR(VLOOKUP(…), “Not found”) returns a clean value instead of an error when the lookup fails, so a broken reference is visible as a message rather than a wall of #REF!.
• Use generous ranges for lookups. A full-column range like A:B survives row deletions better than a tight A2:B20.
• Paste values when the formula is done. If you no longer need live recalculation, select the cells, then Edit → Paste special → Values only. Static values can’t throw a reference error — a useful last step after any bulk data cleanup.

Quick Reference: Cause → Fix

Frequently Asked Questions

Did #REF! delete my data?

No. The underlying values are intact — only the formula’s pointer to them is broken. Fixing the reference (or undoing the delete) restores the calculation. Nothing is permanently lost unless you also deleted the source values themselves.

Why did deleting a column break a formula that doesn’t mention that column?

The formula probably referenced a cell inside the deleted column, or used a range that passed through it. A SUM(B2:D2) still “touches” column C even though C isn’t named explicitly, so deleting C breaks it. Read the formula bar to see the full range each formula uses.

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

They print directly in the cells, so a visual scan works for small sheets. For a large one, add a temporary helper column that wraps the risky formula — =IFERROR(the_formula, “BROKEN”) — then filter for “BROKEN” to list every failing row in one place.

Can I recover after closing and reopening the file?

Yes, through version history: File → Version history → See version history. Pick a snapshot from before the change that introduced the error. This works even after you’ve closed the tab, as long as the file is saved to Drive.

What’s the difference between #REF!, #NAME?, and #VALUE!?

#REF! is a dead cell reference. #NAME? means Sheets doesn’t recognize a function or named range — usually a typo in the function name. #VALUE! means the formula got the wrong data type, like text where it expected a number. Each points to a different kind of mistake, so the error code tells you where to look first.

Sources:

• Fix formula errors — Google Docs Editors Help