#REF! Error
The reference is gone. Cell deleted, sheet removed, column shifted — whatever your formula was pointing at doesn't exist anymore. #REF! is the worst error because it's usually unrecoverable without CTRL+Z. Here's how it happens and how to prevent it.
Signals that a cell reference in your formula points to a location that no longer exists. Unlike other errors, #REF! erases the original reference — the formula now literally contains #REF! as its argument, and Excel can't tell you what it used to point to.
What #REF! means
When Excel evaluates a formula, it looks up each cell reference to get a value. #REF! appears when Excel can't find the referenced location — the cell, row, column, or sheet has been deleted. Unlike #VALUE! (which is about data type) or #NAME? (which is about function names), #REF! is uniquely destructive: Excel replaces the original reference with the literal text #REF!. There's no history. You can't ask Excel "what did this formula used to reference?" — it doesn't know anymore.
This is why CTRL+Z (undo) is your best friend when you see #REF! appear. If you catch it right after the deletion that caused it, undo restores everything. Wait too long — save the file, close it, come back later — and recovery becomes much harder. Sometimes impossible without a backup.
⚡ The moment you see #REF!, press CTRL+Z
Nine times out of ten, #REF! appears immediately after you delete a row, column, or sheet. Undo restores everything perfectly. This is the single most important reflex in Excel: if you delete something and formulas break, undo immediately. Every second you wait, you risk making other changes that can't coexist with an undo.
The 5 root causes of #REF!
Your formula referenced B10, you deleted column B, and now the formula shows =#REF!*2. Excel has no way to know what should replace B — it's gone. Most common cause of #REF! by far.
Formula references =Sheet2!A1, then you delete Sheet2. All formulas depending on it become =#REF!!A1. Renaming Sheet2 to something new sometimes triggers this if Excel can't reconcile the reference.
You copied a formula referencing A1 from row 5 to row 1. Excel tries to shift the reference relative — but there's no row -4. Result: #REF!. Also happens shifting formulas past column A leftward.
Formulas like =INDIRECT("Sheet2!A1") return #REF! if the sheet name is misspelled, doesn't exist, or was deleted. INDIRECT can't error-check at write-time; it fails at evaluation time.
Your formula references a cell in another workbook that has been renamed, moved, or deleted. Excel can't find the source. Depending on version, this may show #REF! or a broken-link warning first.
5 real-world recovery scenarios
Row deleted, formulas broke
You deleted row 5 to "clean up", now dozens of downstream formulas show #REF!.
If it's too late for undo: use Find & Replace to change #REF!:B20 to B5:B20 across all cells. Be surgical — verify each formula rather than mass-replacing.
Sheet gone, downstream formulas broken
You deleted "OldData" sheet without realizing other sheets referenced it. Now every formula references #REF!.
Prevention: before deleting any sheet, use Formulas → Trace Precedents on a couple of sample cells to confirm nothing depends on it.
Formula copied to row 1, references go negative
Formula in B10 was =B5*2 (relative reference). You copy-pasted to B3 — Excel tries to shift the reference to B-2, which doesn't exist.
Better fix: use Table structured references (=Sales[Amount]) which are immune to row/column shifts.
Dynamic sheet reference misfires
You built a formula like =INDIRECT("Sheet"&A1&"!B2") and A1 changed to a value where no matching sheet exists.
Better yet: use SWITCH or XLOOKUP to map A1 to allowed values, avoiding INDIRECT entirely.
VLOOKUP returning column that doesn't exist
Your VLOOKUP uses column 5, but the lookup range only has 4 columns after someone deleted a column in the source table.
Long-term fix: XLOOKUP references columns by their range, not by number. Immune to structural changes. Read our XLOOKUP guide.
Prevention checklist
How to build workbooks that never break
- Use Excel Tables and structured references.
=Sales[Amount]never breaks when rows/columns shift. Insert → Table converts any range. - Prefer XLOOKUP over VLOOKUP. XLOOKUP references columns by their range, not by position. Column deletions don't break it.
- Use absolute references for cells that shouldn't move.
$B$5stays anchored during copy-paste;B5shifts. - Trace Precedents before deleting anything. Formulas ribbon → Trace Precedents shows what depends on the cell you're about to delete.
- Use "Delete Contents" not "Delete Row" when possible. Deleting content clears values but keeps structure — no #REF!.
- Name your critical ranges. Formulas → Define Name creates a permanent bookmark. Formulas using the name survive row/column changes.
- Enable AutoSave. If #REF! happens hours before you notice, version history lets you recover.
#REF! vs other errors
Version compatibility
#REF! has existed since the earliest versions of Excel and behaves identically across all modern versions.
Related errors and resources
Frequently asked questions
Can I recover the original reference after #REF! appears?
Only through undo (CTRL+Z) or a backup. Once #REF! is written into the formula, Excel has no memory of what used to be there. The literal text "#REF!" replaces the reference. Recent AutoSave versions can help; otherwise, you're rebuilding manually.
Why do I get #REF! when copying formulas?
Because the reference tried to shift beyond the sheet boundary. Formula in B5 was =A4, copied to B2 — Excel tried to shift to A1 (fine), copied to B1 tried to shift to A0 (doesn't exist), returned #REF!. Use absolute references (=$A$4) or Table structured references to avoid this.
How do I find all #REF! errors in a workbook at once?
Home ribbon → Find & Select → Go To Special → Formulas → Errors. Excel selects every cell containing an error. Or use Find & Replace (CTRL+F) with search value "#REF!" and "Look in: Formulas" to locate all instances.
Does IFERROR fix #REF! errors?
Only cosmetically. IFERROR hides the error and returns a fallback, but the underlying broken reference is still broken. Use IFERROR sparingly — always fix the root cause when you can.
Can Excel Tables prevent #REF! errors?
Largely yes. Structured references like Sales[Amount] refer to a named column, not a row/column position. Delete a row and the reference still finds "Amount". This is the single biggest thing you can do to prevent #REF! errors across a workbook.
Why does INDIRECT sometimes give #REF!?
INDIRECT evaluates its string argument at runtime. If the string points to a nonexistent sheet, cell, or range name, INDIRECT returns #REF!. Verify the constructed string by putting it in a helper cell first — if the concatenated result shows something like "Sheet99!A1" and Sheet99 doesn't exist, you found the issue.
Can I disable Excel's ability to create #REF! errors?
No. #REF! is fundamental to how Excel handles broken references. What you can do: use structural safeguards (Tables, named ranges, XLOOKUP, absolute references) so that most operations don't create #REF! in the first place. Prevention is the entire strategy.
Never fear a delete key again.
The Add-in flags formulas that would break BEFORE you delete rows, columns, or sheets — no more #REF! disasters after a "quick cleanup."
Get the Add-in →