Guides
How to Fix #REF! Errors in Excel: Causes, Finding Them All, Fixing in Bulk
Updated 2026-10-18
#REF! is Excel saying the thing this formula refers to is gone. Unlike #DIV/0! or #N/A, it usually doesn't go away when the data changes.
Common causes
- Deleted rows, columns or cells that were referenced:
=B5*2 becomes =#REF!*2 when row 5 is deleted.
- A deleted sheet: every formula pointing at it turns into
#REF!.
- Cut and paste over a referenced range.
- Copied formulas whose relative references run off the sheet: copy
=A1 from row 2 up to row 1 and it would point above row 1.
- VLOOKUP or INDEX asking for a column that isn't there:
=VLOOKUP(x,A:C,5,0) wants column 5 of a 3-column range.
- External links to a file or sheet that no longer exists.
The first two are the most common, and they share one important trait: the lost reference is written into the formula (the formula text itself contains #REF!), so apart from Undo there's no way to get the original address back automatically.
Find all of them at once
Go To Special:
- Select the whole sheet (Ctrl+A).
- F5 → Special → Formulas, tick only Errors, OK.
- Every formula showing an error is selected; give them a fill color to mark them.
Find: Ctrl+F, search for #REF!, Options → Look in: Formulas, Find All. This lists the cells whose formula text contains #REF!, i.e. the broken formulas.
Both work on the current sheet only, so with many sheets you repeat them on each.
How to fix
- You know what it used to point at: replace
#REF! in the formula with the right address.
- The old data isn't needed any more: replace the formulas with the values they should have, or delete them.
- You only want totals to stop breaking: wrap the formula in
IFERROR(formula, 0). That hides the error but the reference is still broken, and IFERROR also hides other errors you might want to see.
To prevent it: before deleting rows, columns or sheets, use Formulas → Trace Dependents to see whether any formula uses them.
Check and fix in bulk
xlszip's check-up scans the whole workbook:
- counts each kind of error value (
#REF!, #DIV/0!, #N/A, #NAME? and so on);
- lists broken formulas separately, those with
#REF! written into their text, which recalculation can't recover;
- also checks circular references, manual calculation, volatile functions and more.
Then fix them right on the page:
- broken formulas and structural errors such as
#REF! / #NAME? are frozen to the value they show: the display stays the same but the broken formula is gone, ready for you to type the right value;
- with the fix option ticked, calculation errors such as
#DIV/0! are wrapped in IFERROR with the original formula kept, taking effect when Excel recalculates;
- every change is verified cell by cell, and the page shows how many of each kind were fixed.
If you just want a clean-looking sheet to send out, Beautify can clean error displays too.