Excel #REF! Error: Broken References Explained
By Szabó Gergő · Updated
#REF! means a cell reference no longer exists. Deleting a row, sheet, or copied range that a formula pointed to is the usual cause.
Syntax and arguments
=INDEX(remaining_range, row_num, [column_num])- remaining_range
- A range that still exists after the deletion.
- row_num
- A position inside that range, not a deleted worksheet row.
#REF! examples
Column index 4 now points at a removed field.
=XLOOKUP(G2,A2:A100,C2:C100)XLOOKUP uses the return range directly, so a deleted middle column does not shift col_index_num.
A formula still points at [Budget.xlsx]Sheet1!A1.
=IFERROR(A1, "Open Budget.xlsx")Reopen the source file or replace the link with a local copy of the values.
Old formula was =B10 and row 10 is gone.
=INDEX(B:B,10)INDEX on a full column survives some row edits better than a hard B10, but confirm the intended record.
Common mistakes
Deleting source rows used by charts and formulas together
Clear values instead of deleting structure, or convert to a Table so references resize.
Copying formulas that still point at a temporary sheet
Paste values before deleting the scratch sheet.
Ignoring #REF! inside a larger IFERROR
Find #REF! with Go To Special, Formulas, Errors before wrapping.
#REF! FAQ
Can Undo fix #REF!?
Immediately after a delete, Undo restores the reference. After save and close, rebuild the formula.
Do Tables prevent #REF!?
Structured references resize with the Table, which avoids many deleted-row #REF! cases.
Why did INDIRECT become #REF!?
The sheet or named range in the text address is missing. Check the name exactly.