Fix the #REF! Error and Stop It Coming Back
Ctrl+Z. Right now, before you do anything else.
#REF! is different from every other Excel error, and this is why: when the thing a formula pointed at is deleted, Excel doesn't leave the formula broken — it rewrites it, replacing the reference with the literal text #REF!.
Before: =D4+E4
After: =#REF!+E4The formula no longer records what it used to point at. Nothing in the file does. You can't inspect it, repair it, or work it out — you can only rewrite it from your own knowledge of what it was meant to do.
Undo is the only real recovery, and it stops working the moment you close the file.
Five causes, one error. The middle column is what each formula actually contains now — and in the first two, the reference has been replaced by the error itself.
Where they come from
| Cause | Recoverable by undo? |
|---|---|
| A column or row was deleted | Yes, immediately |
| A sheet was deleted | Yes, immediately |
| Cut cells were pasted over referenced cells | Yes, immediately |
| VLOOKUP asked for a column outside its range | Not needed — fix the formula |
| INDEX or OFFSET pointed outside the data | Not needed — fix the formula |
The bottom two are a different animal. Nothing was destroyed; the formula is simply asking for something that isn't there, and editing it fixes it. The top three destroy information.
The cut-and-paste one
Worth understanding because it surprises people.
Copying onto a referenced cell is safe — the formula reads whatever is there now.
Cutting and pasting onto a referenced cell is not. Cut removes the original cells, and any formula pointing at the destination loses its target. The paste looks like an ordinary move; the damage appears somewhere else in the workbook.
If you're rearranging a sheet other formulas depend on, copy, paste, then delete — three steps, and every one of them undoable on its own.
The VLOOKUP column number
=VLOOKUP($A2, $G$2:$I$8, 5, FALSE)The range is three columns wide. The formula asks for the fifth. There is no fifth.
This happens most often to a formula that used to be correct — someone deleted a column inside the lookup range, so a range that was five columns is now three, and the hardcoded 5 is suddenly out of bounds.
The durable fix is to stop hardcoding it. Either let MATCH find the column:
=VLOOKUP($A2, $G$2:$I$8, MATCH("Amount", $G$1:$I$1, 0), FALSE)Or use INDEX MATCH, which has no column number to get wrong:
=INDEX($I$2:$I$8, MATCH($A2, $G$2:$G$8, 0))This is the strongest practical argument for INDEX MATCH over VLOOKUP, and it isn't about elegance — a hardcoded column number is a #REF! waiting for someone to reorganise a sheet.
Finding every one of them
One deleted column can break formulas on sheets you haven't opened in months. Find them all at once:
- Ctrl+F
- Search for
#REF! - Click Options, set Within to Workbook and Look in to Formulas
- Find All
⚠️ Step 3 matters. Leave "Look in" set to Values and you'll find only cells currently displaying the error. Set to Formulas, it finds every formula with #REF! baked into it — including ones wrapped in IFERROR, which are showing a tidy blank while being completely broken.
That last case is the reason to check even when the sheet looks fine.
Stopping it happening again
Trace dependents before you delete
Select the column you're about to remove, then Formulas → Trace Dependents. Arrows appear pointing at every formula that reads it. No arrows means it's safe to delete.
Ten seconds, and it turns an irreversible action into an informed one.
Hide rather than delete
A column nobody wants to see doesn't need removing. Hiding it keeps every formula intact, and the data is still there when someone asks where the figure came from.
If it really must go, clear its contents rather than deleting the column. Formulas then return zero or blank rather than #REF!, and you can see which ones were affected.
Use Tables
Format your data as a Table with Ctrl+T and reference it by name:
=SUM(Orders[Amount])That reference survives columns being inserted, moved, or deleted elsewhere in the table. Delete the Amount column itself and it still breaks — nothing prevents that — but the everyday reorganisation that produces most #REF! errors stops mattering.
Version history
If the file lives on OneDrive, SharePoint or Google Drive, you have version history, and it is the backstop for the day undo isn't available.
In Excel: File → Info → Version History. Open an earlier version, find the formula, copy it out. Learn where this is before you need it.
Does this work in Google Sheets?
Sheets produces the same error and behaves the same way, with one significant advantage: version history is always on, free, and granular. File → Version history → See version history will show you the formula as it was before someone deleted the column.
That single feature makes #REF! far less costly in Sheets than in a locally saved Excel file.
When it goes wrong
I closed the file before undoing
Undo is gone. Version history if you have it; otherwise rewrite the formula. Check whether the same formula exists elsewhere in the workbook first — one intact copy of a formula that was filled across a row is enough to reconstruct the rest.
Everything went #REF! at once
A sheet was deleted, or a column that half the workbook depended on. Undo, then check what was pointing at it before trying again.
The error came back after I fixed it
The formula was fixed but the cause wasn't. Something is still deleting or overwriting the target — often a refresh or a macro that rebuilds the sheet.
A formula shows blank but the sheet is wrong
An IFERROR is hiding a #REF!. Search Formulas, not Values.
Excel says #REF! and Google Sheets doesn't
The two disagree on some out-of-range arguments. If a converted file changes its errors, the underlying problem is still there — fix the formula rather than trusting whichever program complains less.
Common questions
Can I find out what the formula used to say?
Not from the file. That information was overwritten when the reference broke. Version history or your own memory are the only sources.
Should I wrap lookups in IFERROR to prevent this?
No — that hides #REF! without fixing it, which is worse than seeing it. Use IFNA where you want to hide an expected miss, as the #N/A guide covers, and let #REF! show.
Why does deleting a row break formulas that don't reference it?
They probably reference a range that contained it. Deleting row 10 from a formula reading SUM(A5:A20) shrinks the range harmlessly, but deleting a row a formula points at directly leaves nothing to point at.
Is there a way to make Excel warn me before deleting?
No built-in warning. Trace Dependents is the manual equivalent, and it's worth the habit on any sheet other people rely on.
The short version
- Ctrl+Z immediately. It is the only reliable recovery.
- Excel rewrites the formula when a reference is deleted. The original is not stored anywhere.
- Copy-paste onto a referenced cell is safe; cut-paste is not.
- A hardcoded VLOOKUP column number is a #REF! waiting to happen. Use MATCH, or INDEX MATCH.
- Find them all with Ctrl+F, Look in: Formulas — Values misses the ones hidden by IFERROR.
- Trace Dependents before deleting anything.
- Hide columns rather than deleting them.
- Know where your version history is before you need it.
Next steps: the other lookup error behaves completely differently — #N/A is often the correct answer, where #REF! never is. Rebuilding a broken lookup properly is covered here, and INDEX MATCH is what stops the column-number version recurring.
Comments
Post a Comment