Fix the #REF! Error

Excel & Google Sheets

How it works

#REF! means the cells a formula referenced were deleted, so the reference itself is destroyed — you'll see it embedded in the formula, like =VLOOKUP(A2,#REF!,3,FALSE). There's nothing left to point at, so the fix is to restore or re-point. If the deletion just happened, undo (Ctrl+Z / Cmd+Z) brings everything back. Otherwise, edit the formula and rebuild the broken reference to where the data lives now. To prevent the next occurrence, prefer structures that survive deletion: INDEX + MATCH references the lookup and return columns directly, so deleting an unrelated column between them breaks nothing — unlike VLOOKUP's positional column number.

Undo first

If the deletion is recent, Ctrl+Z restores the cells and every formula heals itself.

Find #REF! inside the formula

The broken argument shows exactly which reference was destroyed — retype it to point at the data's new home.

INDEX + MATCH

Prevention: names each column explicitly, so deleting other columns can't break the lookup.

When to use it

Follow this recovery path whenever #REF! appears after restructuring a sheet — and switch high-value lookups to INDEX + MATCH or XLOOKUP so the next cleanup doesn't break them.

Common mistakes

• ✕Deleting whole columns to remove unwanted data.

  ✓Deleting the column destroys every reference into it. Clear the contents instead (Delete key), or hide the column.

• ✕Copying a formula with relative references to a place they can't reach.

  ✓Pasting =A1-B1 into row 1's neighbor can push references off the sheet edge, causing #REF!. Anchor with $ before copying.

• ✕Rebuilding the formula but keeping VLOOKUP's fragile column number.

  ✓The repaired VLOOKUP breaks again on the next column deletion. Rebuild with XLOOKUP or INDEX + MATCH instead.