How to fix the #REF! error in Excel
#REF! means a reference is invalid. Usually a row, column or sheet the formula used has been deleted, or a formula was copied somewhere its references fall off the sheet.
#REF!
Why it happens
Deleted rows, columns or sheets
If =A2+C2 and you delete column C, the formula becomes =A2+#REF!.
VLOOKUP column number too big
=VLOOKUP(1006,A2:C9,5,FALSE) asks for column 5 of a 3-column table.
Copied formulas falling off the edge
Copying =A1 up from row 1 has nowhere to go.
Closed or renamed external workbooks
Links to another file break when it moves or is renamed.
How to fix it
Rebuild it with a range reference, which survives deleted rows
=SUM(F2:F9) → 657
Keep the column number inside the table
=VLOOKUP(1006,A2:F9,6,FALSE) → 54
Use XLOOKUP, which doesn’t rely on column numbers
=XLOOKUP(1006,A2:A9,F2:F9) → 54
Examples use this sample sheet of orders:
A
B
C
D
E
F
1
Order
Region
Rep
Units
Price
Revenue
2
1001
East
Maya
4
12
48
3
1002
West
Omar
2
30
60
4
1003
East
Lena
7
12
84
5
1004
North
Maya
1
45
45
6
1005
West
Lena
5
30
150
7
1006
East
Omar
3
18
54
8
1007
North
Lena
6
12
72
9
1008
West
Maya
8
18
144
Tip: Whole-range references like SUM(F2:F9) survive deleted rows inside the range; individual cell references like F2+F3+F4 don’t.
Other Excel errors
- #N/A“Not available”: a lookup couldn’t find what you asked for.
- #VALUE!A formula got the wrong kind of data, usually text where it needed a number.
- #DIV/0!A formula tried to divide by zero or by an empty cell.
- #NAME?Excel doesn’t recognise a word in the formula.
- #SPILL!A formula that returns several results has no room to spill into.
- #NUM!The math is impossible or a number is out of range.