Excel #REF! error: fix broken cell references
Why formulas suddenly show #REF! after deleting or moving data, and how to repair them.
The message you see
#REF!Fastest fix
Upload the file and see exactly which rows cause the problem. Free, and the file stays in your browser.
Why this happens
- A row, column or sheet the formula referred to was deleted.
- Cells were cut and pasted over referenced cells.
- A VLOOKUP column index is larger than the lookup range.
- A link to another workbook no longer exists.
How to fix it
- 1Press Ctrl+Z right away if you just deleted something.
- 2Use Find (Ctrl+F) for “#REF!” to locate every broken formula.
- 3Rewrite the reference to the correct range, or use names / XLOOKUP so inserts don't break it.
- 4Check Data → Edit Links for broken external links.
Related guides
Excel
“Excel cannot open the file because the file format or file extension is not valid”
ExcelExcel error “SYLK: File format is not valid” when opening a CSV
Excel“We found a problem with some content” — Excel workbook won't open properly
Still stuck? Ask SheetFix Support