Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the #REF! error
Excel & Google SheetsWhat #REF! means
A #REF! error means a formula is referring to a cell or range that no longer exists. The reference broke — usually because the cells it pointed to were deleted or moved out from under it.
Common causes
- You deleted a row, column, or sheet that a formula referenced.
- You cut and pasted cells over the range a formula depended on.
- A VLOOKUP or INDEX uses a column number that is larger than the table is wide.
- A reference points to a closed or deleted external workbook.
Example fix
Broken
=VLOOKUP(D2, A:B, 3, FALSE)Fixed
=VLOOKUP(D2, A:B, 2, FALSE)The table A:B has only 2 columns, so column index 3 is out of range. Use 2.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Click the cell showing #REF! and read the formula — the broken part appears literally as #REF! inside it.
- 2Press Ctrl+Z (Cmd+Z) immediately if the error appeared right after deleting a row, column, or sheet; that restores what the formula needed.
- 3If undo is not an option, retype the reference to point at the correct, still-existing cell or range.
- 4In VLOOKUP, make sure the column index number is not greater than the number of columns in your table range.
- 5Wrap the formula in IFERROR only after you have fixed the real reference — never use it to hide a #REF! you have not understood.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every #REF! and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
Does #REF! mean my data is lost?
Not necessarily. The formula lost its reference, but the underlying data may still exist elsewhere. If the error appeared right after a deletion, undo (Ctrl+Z) usually restores it.
How do I find every #REF! error in a large sheet?
Use Find & Replace (Ctrl+H) to search for "#REF!", or upload the file to the FormulaCraft Auditor to list every broken formula at once.