How to Fix the #REF! Error in Excel
The #REF! error means a formula points to a cell that no longer exists — usually because a row, column, or cell it referenced was deleted. The fastest fix is Ctrl+Z; if that is too late, you rebuild the broken reference. Here is how.
#REF! is Excel telling you a formula is pointing at nothing. It almost always appears right after you delete a row, column, or cell that a formula depended on. The good news: it is one of the most fixable errors, because Excel usually shows you exactly where it broke.
What causes #REF!
- You deleted a row, column, or cell that a formula referenced.
- You cut and pasted cells over a range a formula used.
- A VLOOKUP or INDEX asks for a column number that does not exist (see how to use VLOOKUP).
- A link to another workbook that was moved or deleted.
The fastest fix: undo
If the #REF! just appeared because you deleted something, press Ctrl+Z immediately. This restores the deleted cells and the reference repairs itself. This only works before you close the file or do too many other actions.
If undo is too late: find and rebuild
- Press Ctrl+F, type #REF!, and search within Formulas to find every broken cell.
- Click a broken formula and look for the literal text #REF! inside it — that is the piece that lost its target.
- Replace #REF! with the correct cell or range the formula should point to now.
- Use Find & Replace (Ctrl+H) to fix many identical broken references at once.
Frequently asked questions
Does #REF! mean my file is corrupted?
No. #REF! is a normal formula error, not file damage. It simply means a formula points to a cell that no longer exists. Fixing or removing that reference clears it.
How do I find all #REF! errors at once?
Press Ctrl+F, search for #REF! and set "Look in" to Formulas. Excel lists every cell containing the broken reference so you can fix them one by one, or use Find & Replace for repeated ones.