#REF!
#REF! Error in Excel — Causes & Fixes
Excel shows #REF! when a formula contains a cell reference that no longer exists — usually because a row or column was deleted.
Why this happens
Common causes of #REF!
Rows or columns were deleted
If a formula references a cell that has been deleted, Excel cannot recalculate and shows #REF!. The reference becomes =A1:#REF! in the formula bar.
VLOOKUP column index is too large
If the col_index_num in VLOOKUP is larger than the number of columns in table_array, Excel returns #REF!. For example, =VLOOKUP(A1,B:D,5,FALSE) asks for the 5th column of a 3-column range.
Circular reference through a deleted cell
Pasting or moving formulas sometimes creates a reference that points to itself or to a cell that no longer exists.
INDIRECT pointing to a deleted sheet
INDIRECT references are evaluated at runtime. If the target sheet name changes or the sheet is deleted, INDIRECT returns #REF!.
How to fix it
Step-by-step fixes for #REF!
Undo the deletion with Ctrl+Z
If you just deleted the row or column, press Ctrl+Z immediately to restore it and fix the reference.
Rebuild the formula manually
Click on the cell with #REF!, look at the formula bar, and replace the #REF! fragment with a valid cell or range reference.
Use named ranges to protect against deletions
Name important ranges (Ctrl+F3) and reference them by name instead of cell address. Named ranges update automatically when rows or columns move.
Fix VLOOKUP column index
Count the number of columns in your table_array and make sure col_index_num does not exceed that count. Switch to INDEX/MATCH to avoid this class of error entirely.
Frequently asked questions
How do I find all #REF! errors in a worksheet?
Press Ctrl+F, type #REF!, and click Find All. Excel will list every cell containing the error so you can fix them one at a time.
Can I prevent #REF! errors from breaking my model?
Use IFERROR to show a fallback, but fix the root cause — a #REF! error means data your formula needs is missing, so hiding it can mask real problems.
Related function references
These functions appear most often in formulas that produce #REF!.
Other Excel errors