Excel #REF! Error: What Broke Your Cell References and How to Repair Them

Q: Can I find every #REF! at once?

Yes. Press Ctrl + F, type #REF!, set Within: Workbook and Look in: Formulas, then Find All to list every cell that contains a broken reference.

Published ✓ Verified ⏱️ 3 min read October 10, 2025 · by Ryan Bennett

A formula suddenly displays #REF! — often right after you deleted a row, column, or worksheet.

Why: #REF! means a formula refers to a cell that is no longer valid. Most often the referenced cells were deleted or pasted over, so the formula has nothing to point at. It can also appear when a VLOOKUP or INDEX argument points outside the range you gave it, or when a link reaches into a workbook that’s closed.

Fix 1: Undo the deletion that broke it

If the #REF! appeared the instant you deleted rows or columns, the fastest fix is to put them back.

Press Ctrl + Z, or click the Undo button on the Quick Access Toolbar.
The deleted cells return and the formula recalculates to its correct value.

If you’ve made other edits since and can’t undo cleanly, retype the formula with the correct references — the #REF! text appears literally inside the formula wherever the dead reference was, so you can see exactly which part to replace.

Fix 2: Use range references so deletes don’t break formulas

Individual cell references break when a referenced cell is deleted. A range reference survives, because Excel automatically shrinks the range.

Fragile:  =SUM(B2,C2,D2)     ← deleting column C gives #REF!
Robust:   =SUM(B2:D2)        ← deleting a column inside just adjusts the range

Rewrite key totals as ranges. Now you can delete any column within the sum range and Excel adjusts the formula instead of breaking it.

Fix 3: Fix an out-of-range VLOOKUP or INDEX argument

A #REF! with no deletion involved usually means the formula asks for a column or row that doesn’t exist inside its range.

VLOOKUP — the column index is larger than the table width:

Broken: =VLOOKUP(A8,A2:D5,5,FALSE)   ← range A:D is only 4 columns, asking for col 5
Fix A:  =VLOOKUP(A8,A2:E5,5,FALSE)   ← widen the range to 5 columns
Fix B:  =VLOOKUP(A8,A2:D5,4,FALSE)   ← or pull from a column that exists

INDEX — the row/column number falls outside the range:

Broken: =INDEX(B2:E5,5,5)   ← the range is only 4 rows by 4 columns
Fix:    =INDEX(B2:E5,4,4)   ← keep both numbers inside the range

Fix 4: Open the workbook a link points to

If the formula uses INDIRECT or a link to another file, #REF! can appear because the referenced workbook is closed (INDIRECT can’t read closed files at all). Open the referenced workbook, then recalculate with F9. For ordinary links, Data > Edit Links lets you repoint a broken source to the correct file.

FAQ

Can I find every #REF! at once? Yes. Press Ctrl + F, type #REF!, set Within: Workbook and Look in: Formulas, then Find All to list every cell that contains a broken reference.

I undid the delete but #REF! is still in the formula. Undo only restores cells; if the formula was edited and saved with #REF! baked into its text, you must retype that portion manually with the right reference.

Will deleting a whole sheet cause this? Yes — formulas on other sheets that referenced the deleted sheet turn into #REF!. Undo immediately, or recreate the sheet and repoint the references.

Sources: Microsoft Support — How to correct a #REF! error · Microsoft Support — How to avoid broken formulas in Excel