Cellwise

Excel guide · 3 min read

#REF! error in Excel

#REF! means a formula points to a cell that is not valid: it was deleted, or the formula asks for a column or row outside its range. Press Ctrl+Z right after deleting to undo it; otherwise edit the formula, replace each #REF! with the correct cell, and use XLOOKUP or INDEX/MATCH so inserting or deleting columns no longer breaks it.

Sample data

Every formula on this page was calculated live against this sheet.

ABC
1EmployeeDeptSalary
2Hana ItoFinance68000
3Luis OrtegaSales54500
4Grace KimIT81000
5Noah ReedSales49000

Cause 1: you deleted cells a formula used

Say D2 holds =C2*1.05 and you delete column C. Excel cannot point at a column that no longer exists, so it rewrites the formula as =#REF!*1.05 and shows #REF!. The same happens when you delete rows used by a formula, or paste over the cells it referenced.

Clearing a cell's contents (Delete key) does not cause this. Only deleting the cell itself (right-click > Delete) does.

  1. Press Ctrl+Z immediately to undo the delete, if you still can.
  2. Otherwise click the #REF! cell and look at the formula bar to see which reference became #REF!.
  3. Replace #REF! with the correct cell, or re-enter the data you deleted and point at it again.

Cause 2: VLOOKUP column number beyond the table

The table A2:C5 has three columns, but this formula asks for column 4. VLOOKUP cannot return a column that is not in its range, so it returns #REF!.

This often happens after you shrink the table or copy the formula from a wider sheet.

fx=VLOOKUP("Grace Kim",A2:C5,4,FALSE)

Result: #REF!

A2:C5 has only 3 columns, so asking for column 4 gives #REF!.

Fix: correct the column, or switch to XLOOKUP

Change 4 to 3 to return the salary. Better still, XLOOKUP (Excel 365 and 2021) points directly at the return column, so there is no column number to get wrong, and it adjusts when columns move.

fx=XLOOKUP("Grace Kim",A2:A5,C2:C5)

Result: 81000

XLOOKUP finds Grace Kim and returns her salary from C: 81,000.

Cause 3: INDEX asked for a row that is not there

INDEX(C2:C5,6) asks for the 6th item of a 4-item range. That is outside the range, so INDEX returns #REF!. Usually the row number comes from a MATCH or a typed constant that no longer fits the data.

fx=INDEX(C2:C5,6)

Result: #REF!

C2:C5 only has 4 rows, so row 6 is out of range and the result is #REF!.

Fix: let MATCH work out the position

Let MATCH find the row instead of hard-coding it. It can only return positions that exist in the range, so INDEX cannot overshoot.

fx=INDEX(C2:C5,MATCH("Noah Reed",A2:A5,0))

Result: 49000

MATCH finds Noah Reed at position 4, and INDEX returns his salary: 49,000.

Find every #REF! on a sheet

Copying a formula with relative references up or left past row 1 or column A also creates #REF!, because there is no cell above row 1. These hide easily in big sheets, so search for them.

  1. Press Ctrl+F, type #REF!, click Options, and set Look in to Formulas.
  2. Click Find All to list every formula that contains a broken reference.
  3. Or use Home > Find & Select > Go To Special > Formulas, with only Errors ticked, to select all error cells.

Common mistakes

Using IFERROR to hide #REF!

A #REF! is a broken formula, not missing data. Hiding it leaves a wrong number in your report. Repair the reference instead.

Hard-coded column numbers in VLOOKUP

Insert or delete a column and the number points at the wrong place. Use XLOOKUP or INDEX/MATCH so the reference follows the column.

Deleting a sheet other formulas use

Formulas on other sheets that referenced it all turn to #REF!, and you cannot undo a sheet delete. Check with Formulas > Trace Dependents first.

FAQ

Can I undo a #REF! error?

Yes, if you press Ctrl+Z right after the delete that caused it. Once you have saved and closed, the original reference is gone and you have to retype it.

Why does copying a formula give #REF!?

Relative references shift when you copy. If a formula refers to the cell above and you paste it into row 1, there is no cell above, so Excel shows #REF!. Use $ to lock references that should not move.

What is the difference between #REF! and #N/A?

#REF! means the formula points at an invalid cell. #N/A means the formula is fine but the lookup value was not found.

Functions used

Keep learning

Updated 2026-10-31