The #REF! error means a formula is referring to a cell that no longer exists. It's one of the most common Excel errors and usually appears right after you delete something. Here's what causes it and how to fix it.
What #REF! actually means
#REF! is short for "invalid reference." It appears when a formula points to a cell location that has been removed, so Excel literally has nothing to point at. The reference is broken, not just wrong.
The most common causes
You deleted a row, column, or cell that a formula referenced. This is the classic cause. If a formula was =A1+B1 and you delete column B, the formula becomes =A1+#REF! because the thing it pointed at is gone.
You deleted a sheet another formula referenced. A formula like =Sheet2!A1 breaks to #REF! if Sheet2 is deleted.
A cut-and-paste overwrote referenced cells, or a paste landed somewhere that invalidated references.
A lookup with a column index beyond the table. VLOOKUP with a col_index_num larger than the table's width returns #REF! — for example asking for column 5 of a 4-column range.
How to fix it
Find the broken reference. Click the cell with the error and look at the formula bar. You'll see #REF! sitting where a valid reference should be, like =A1+#REF!. That tells you exactly which part broke.
Undo, if it just happened. If the #REF! appeared immediately after you deleted something, press Ctrl+Z to undo the deletion. This is the fastest fix when you catch it right away — it restores whatever the formula was pointing at.
Rewrite the reference. If you can't undo, edit the formula and replace the #REF! portion with the correct cell reference. You'll need to know what the formula was supposed to point at.
Fix lookup column numbers. If a VLOOKUP shows #REF!, check that its col_index_num isn't larger than the number of columns in the table_array.
Finding all of them at once
If #REF! errors have spread across many cells, find them all with Find & Replace:
- Press Ctrl+H.
- In "Find what," type
#REF! - This lets you locate every instance so you can fix them systematically.
Preventing #REF! errors
- Be careful deleting rows and columns in sheets with formulas. Check what references them first.
- Use structured references or named ranges, which are more resilient to structural changes than raw cell references.
- Consider INDEX/MATCH or XLOOKUP over VLOOKUP, since they don't rely on a hardcoded column number that can point past the table.
The takeaway
#REF! almost always means "something I pointed at got deleted." If it just happened, undo. If not, find the #REF! in the formula bar and rebuild that reference. It's a broken pointer, not a calculation mistake — so the fix is always about restoring or replacing the missing reference.