Excel formula returns #REF! after deleting rows or columns
A #REF! error means the formula is pointing at a cell that no longer exists. It usually appears the moment a row, column, or sheet the formula relied on is deleted.
What causes it
A referenced row or column was deleted
If a formula added B2 and C2, and column C is later removed, the reference to it becomes #REF!. The formula cannot repair itself.
A lookup index points past the range
A VLOOKUP asked for column 5 of a range only 4 columns wide returns #REF!. Widen the range or lower the index.
A referenced sheet was deleted or renamed
A formula reading Sheet2!A1 breaks if Sheet2 is removed. Renaming is usually handled automatically, but deleting is not.
A pasted formula ran off the edge of the sheet
Copying a formula that points left, into column A, leaves it pointing off the sheet, which is a #REF!.
What to do
Look at the formula for the literal text #REF! and see what it replaced. If you have just deleted something, undo it, note what the formula needed, and delete more carefully, or rewrite the reference to point at the right place.
Questions
Can I get the old reference back?
Only by undoing the deletion. Once #REF! is in the formula the original address is gone. If you have saved and closed, the reference has to be rebuilt by hand.
How do I avoid this when deleting columns?
Check which formulas depend on a column before removing it. Select the column, and use Formulas, Trace Dependents to see what points at it.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
