How to fix the #REF! error in Excel
What you see: a formula that used to work now displays
#REF!. The name is literal — broken reference. Something the
formula pointed at was deleted, pasted over, or became unreachable. In most cases
Excel cannot recover the old target; the fix is to undo, or rebuild the reference
so it can't happen again.
The fast answer
If you just deleted the rows/columns: press Ctrl+Z (or Undo on the Quick Access Toolbar) immediately — that restores the references. Everything after this point is for when undo is no longer enough.
Cause 1: The referenced cells were deleted or pasted over (most common)
Delete column C and =SUM(B2,C2,D2) becomes
=SUM(B2,#REF!,D2) — the reference survived, its target didn't.
- Try Ctrl+Z first if the change was recent.
- If undo is gone, rewrite the formula with a range reference instead
of individual cells:
=SUM(B2:D2). Ranges shrink cleanly when you delete cells inside them; single-cell references shatter.
Cause 2: VLOOKUP's column number is wider than the range
=VLOOKUP(A2,A:D,5,FALSE) asks for column 5 of a 4-column range.
Per Microsoft's guidance: adjust the range to be larger, or reduce the column
number to match. Count your columns from the left edge of the range — A:D
has exactly 4.
Cause 3: INDEX row/column arguments outside the lookup range
Same math, different function: an INDEX asking for row 50 of a 30-row range
returns #REF!. Adjust the row or column argument so it falls inside
the range you gave INDEX.
Cause 4: INDIRECT pointing at a closed workbook
INDIRECT to another workbook works only while that workbook is open; close it
and every dependent formula flips to #REF!. Fix: open the referenced
workbook — the formulas recover on recalculation. If you need closed-workbook
links, use direct cell references instead of INDIRECT.
Cause 5: Structured table references in linked workbooks
Table and column names in linked workbooks "aren't supported" —
Microsoft lists this as a distinct cause. Replace the structured reference
(=Table1[Sales]) with a plain range in the linked context.
Cause 6: OLE or DDE links
Rare, but real: links to other programs (OLE) or dynamic data exchange (DDE) break when the source program isn't running. Start the linked program; for DDE, verify the topic and check external-content settings in File > Options > Trust Center.
You'll know it worked when
The formula returns values instead of #REF!, and copying it
down the column doesn't produce new errors. To find every #REF! in a workbook:
press Ctrl+F, search for #REF, and set
Within: Workbook.
Still broken?
Post the formula and what changed just before the error on the Microsoft Answers Excel forum — the "what did I just delete" answer usually solves it in one reply.
Bulletproof-workbook training
Most #REF! disasters come from habit, not luck. A short course on range references and structured data pays for itself the first week.
Compare courses →