Undo the deletion: Press Ctrl+Z (Cmd+Z on Mac) immediately to restore the deleted row/column. Then rewrite the formula to not depend on that reference, or restructure the sheet so deletion is safe.

Why does #REF! keep appearing?

You deleted a row, column, or cell that the formula referenced. A VLOOKUP or INDEX has a column/row number larger than the source range. You cut and pasted cells and the target overwrote a referenced range.

How to prevent #REF! in the future?

Use named ranges instead of direct cell references — named ranges survive most deletions. Use XLOOKUP instead of VLOOKUP — it references a result array directly, no column number to break. Before deleting rows or columns, check "Formula > Trace Dependents" to see what depends on them.

Spreadsheet error

How to fix the #REF! error

TL;DR

#REF! means the formula is pointing at a cell or range that got deleted or moved out of bounds. Fix: undo the deletion, or rewrite the formula with the correct reference.

What it means

The #REF! error (short for "invalid reference") appears when Excel or Google Sheets cannot find the cell, row, column, or range that a formula is trying to use. This almost always happens after a delete, cut-paste, or VLOOKUP with a column index that is larger than the table.

Common causes

•You deleted a row, column, or cell that the formula referenced.
•A VLOOKUP or INDEX has a column/row number larger than the source range.
•You cut and pasted cells and the target overwrote a referenced range.
•A formula copied to a new location now points outside the sheet.
•A linked workbook was deleted or renamed.

How to fix it

1.Undo the deletion

Press Ctrl+Z (Cmd+Z on Mac) immediately to restore the deleted row/column.
Then rewrite the formula to not depend on that reference, or restructure the sheet so deletion is safe.

2.Rewrite the reference

Click the cell showing #REF!.
Look at the formula bar for the literal text "#REF!" inside the formula.
Replace it with the correct cell or range.

3.Fix VLOOKUP column index

Count the columns in your lookup table.
Make sure the col_index_num argument is not larger than the table width.
Or switch to XLOOKUP which uses a result array instead of a column number.

Example

Broken=VLOOKUP(A2, B:D, 5, FALSE)Fixed=VLOOKUP(A2, B:D, 3, FALSE)

The range B:D only has 3 columns. Column 5 does not exist, so it returned #REF!. Changed to column 3.

Prevention

Use named ranges instead of direct cell references — named ranges survive most deletions.
Use XLOOKUP instead of VLOOKUP — it references a result array directly, no column number to break.
Before deleting rows or columns, check "Formula > Trace Dependents" to see what depends on them.

Related errors

#NAME?#VALUE!#N/A

Let FormulaPad debug it for you

Paste your broken formula and the error message — get a diff and an explanation in seconds.

Debug a formula