Formula guide · Excel and Google Sheets · Updated May 13, 2026
How to Fix #REF!
Fix #REF! errors caused by deleted cells, broken references, moved ranges, and invalid lookup column numbers.
Quick Answer
#REF! means the formula refers to a cell, row, column, or range that is no longer valid.
Copyable Formula
=VLOOKUP(E2,A2:C8,3,FALSE)Syntax
Check the broken reference and point the formula at a valid range.Excel and Google Sheets
Worked Example
| Cause | Fix |
|---|---|
| Deleted return column | Restore or repoint the range |
| Invalid VLOOKUP column number | Use a column number inside the table |
| Moved sheet | Repair the sheet reference |
Result: A valid formula reference removes the #REF! error.
Steps
- Undo recent row, column, or sheet deletion if possible.
- Inspect the formula for #REF! text.
- Replace the broken reference with a valid range.
Common Mistakes
- Deleting helper columns before checking dependent formulas.
- Using fragile VLOOKUP column numbers in changing tables.
- Not testing copied formulas after moving sheets.
Excel vs Google Sheets Notes
The core idea works in both Excel and Google Sheets, but separators, function availability, and array behavior can vary by account, locale, and version.
Editorial check: This guide was last updated May 13, 2026. Formula behavior can vary by Excel version, Google Sheets rollout, and spreadsheet locale.