The #REF! error
A formula pointed to a cell that disappeared: a deleted row, a cut column, a removed sheet. Excel replaces the reference with #REF!, and the error spreads to everything that depends on it.
What it means
#REF! means the reference is no longer valid. Unlike the other errors, it is written into the formula itself: =A1+#REF!. The original information is lost.
Every formula that uses the result shows #REF! in turn: a single deletion can turn a whole table red.
Where it comes from
- A row, column or sheet deleted while a formula was using it.
- A cut and paste over cells used by formulas.
- A formula copied up or left, whose references fall off the sheet (above row 1).
- VLOOKUP with a column number too large:
=VLOOKUP(A2,B:D,5,FALSE)asks for the 5th column of a range that has 3. - INDEX or OFFSET pointing beyond the range.
- A defined name whose target was deleted: the name is #REF!, and so is every formula using it.
Fixing it
- Right after the deletion: Ctrl+Z, then delete differently (clear the contents rather than the row).
- Later: rewrite the reference in the formula; Excel cannot guess what it pointed to.
- For VLOOKUP: check the column number, or switch to XLOOKUP, which takes the column to return instead of a number.
- For broken names: Formulas, Name Manager, and delete those equal to #REF!.
Tools that help
- Formula audit: every #REF! in the workbook, sheet and cell, with its formula.
- Health check: broken defined names, removed in one click.
- Remove external links: when the broken reference points to another workbook.
Frequently asked questions
- Can I recover what a #REF! formula pointed to?
- Not from the formula: the reference was replaced. Only an older version of the file still contains it; Compare two Excel files helps find the difference.
- How do I avoid #REF!?
- Clear cell contents rather than deleting rows and columns in use, prefer XLOOKUP or INDEX/MATCH to column numbers, and work in structured tables, whose references follow changes.