Fix the #REF! error.
D2What is the #REF! error?
The #REF! error means a formula is pointing at a reference that is no longer valid — a cell, range, or worksheet that has been deleted or moved out of range. Excel can no longer resolve the address, so it writes the literal text #REF! into the formula where the reference used to be.
It is one of the most common "broken formula" errors because it often appears after an edit that seemed harmless — deleting a column, removing a sheet, or pasting over cells that other formulas depended on.
Common causes
1. Deleted rows, columns, or cells
If a formula references =A1+B1 and you delete column B, the reference to B1 is gone and Excel shows =A1+#REF!. This is the number-one cause.
2. VLOOKUP column index too large
A VLOOKUP like =VLOOKUP(E1, A:C, 4, FALSE) asks for column 4 of a 3-column range. There is no column 4, so it returns #REF!. Use a column index within the table.
3. Cut-and-paste over referenced cells
Cutting (Ctrl+X) a block and pasting it over cells that other formulas reference invalidates those references and produces #REF!.
4. Deleted worksheet
A formula like =Sheet2!A1 breaks to =#REF!A1 if Sheet2 is deleted.
5. INDEX or OFFSET out of range
Asking INDEX for a row or column number beyond the array bounds returns #REF!.
How to fix it — step by step
- If it just happened, press Ctrl+Z (Undo). Restoring the deleted cells/rows/columns brings the reference back instantly.
- Open the formula and find the #REF! token. Click the cell, look in the formula bar, and replace
#REF!with the correct cell or range reference. - Check VLOOKUP/INDEX indexes. Make sure the column/row index sits inside the referenced range.
- Wrap in IFERROR for a clean fallback:
=IFERROR(VLOOKUP(E1, A:C, 3, FALSE), "Not found")— but still repair the real reference. - Use Find & Replace (Ctrl+H) to locate every
#REF!across the sheet and fix them one by one.
How to prevent #REF! errors
- Use named ranges or structured table references — they adjust automatically and are far more robust than raw cell addresses.
- Prefer INDEX/MATCH or XLOOKUP over VLOOKUP — they do not break when columns are inserted.
- Before deleting a column or sheet, check whether other formulas depend on it (Formulas → Trace Dependents).
Generate the corrected formula with FormulaAI
Paste a broken formula into FormulaAI, describe what it should do, and it returns a corrected version — for Excel, Google Sheets, or Apple Numbers — often with an IFERROR wrapper and a plain-English explanation of what went wrong.
Frequently asked questions
What does the #REF! error mean in Excel?
The #REF! error means a formula refers to a cell, range, or sheet that no longer exists. It usually appears after you delete cells, rows, columns, or a worksheet that a formula pointed to — Excel replaces the lost reference with #REF! inside the formula.
How do I find what caused a #REF! error?
Click the cell and look at the formula bar: the literal text #REF! appears where the broken reference used to be. If it just happened, press Ctrl+Z (Undo) to restore the deleted cells. Otherwise, replace the #REF! token with the correct cell or range reference.
How do I hide or handle a #REF! error?
Wrap the formula in IFERROR, e.g. =IFERROR(your_formula, 0) or =IFERROR(your_formula, 'Check reference'). This shows a fallback value instead of the raw error — but fix the underlying reference too, because IFERROR only masks the symptom.
Why does VLOOKUP return #REF!?
VLOOKUP returns #REF! when the col_index_num is larger than the number of columns in the table_array — for example asking for column 4 of a 3-column range. Lower the column index or widen the table range.
Can I remove all #REF! errors at once?
Use Find & Replace (Ctrl+H), search for #REF! and review each match — but you still need to point each formula at a valid reference. There is no safe blanket fix; the reference has to be re-established.
Related guides
- VLOOKUP Guide — and why a too-large column index causes #REF!.
- INDEX MATCH Guide — a #REF!-resistant alternative to VLOOKUP.
- SUMIFS with Multiple Criteria — conditional sums across several columns.
- FAQ — more about FormulaAI.