#REF! error in Excel: what it means and how to fix it

#REF! means a formula points at a cell that isn’t valid. Most often the cell existed when the formula was written and was then deleted, with its whole row, column or sheet. Excel can’t guess what should take its place, so it writes #REF! into the formula where the reference was. The other common cause is asking a function for a column or row past the edge of its range.

1. A deleted row or column

The total in E2 was =B2+C2+D2: Q1, Q2 and Q3. Then column C, Q2, was deleted. Q3 moved left into column C, the total moved into D2, and the reference to the deleted column became #REF!:

D2fx=B2+#REF!+C2

Row	A	B	C	D
1	Rep	Q1	Q3	Total
2	Ana	120	150	#REF!

If you just deleted it, press Ctrl+Z and everything comes back. Otherwise, edit the formula and replace #REF! with the right reference, or rewrite it as a range:

A range is the lasting fix. Had the total been =SUM(B2:D2) from the start, deleting column C would have shrunk it to =SUM(B2:C2) and it would have kept working.

2. A column number past the end of the table

VLOOKUP’s third argument counts columns inside its table, starting from 1. A2:B5 has two columns, so asking for column 3 points past its edge:

F2fx=VLOOKUP(E2,A2:B5,3,FALSE)

Row	A	B	C	D	E	F
1	Code	Item	Stock		Find	Stock
2	P-14	Bolts	320		P-22	#REF!
3	P-09	Nuts	1,150
4	P-22	Washers	80
5	P-03	Screws	640

Widen the table so it reaches the column you want:

The same happens when a column number was right until a column was deleted from the table. XLOOKUP avoids it: it takes the return column as a range, so there’s no number to go stale.

3. A row or column past the end of an INDEX range

INDEX returns the item at a given position. Ask for a position the range doesn’t have and it returns #REF!. C2:C5 holds four stock levels, so there is no 5th:

F2fx=INDEX(C2:C5,5)

Row	A	B	C	D	E	F
1	Code	Item	Stock		Find	Stock
2	P-14	Bolts	320		P-22	#REF!
3	P-09	Nuts	1,150
4	P-22	Washers	80
5	P-03	Screws	640

Let MATCH work out the position from the code instead of typing it:

4. A formula copied off the edge of the sheet

Relative references move when you copy a formula. =A1 in B2 means “the cell one up and one left”. Copy it to B1 and it would point one row above row 1, which doesn’t exist, so the copy becomes =#REF!. Copy formulas only where the cells they point at exist, or lock the reference with $ (=$A$1).

5. A deleted sheet or a closed workbook

A formula that reads another sheet, such as =Sales!B4, becomes =#REF!B4 when that sheet is deleted, and a deleted sheet can’t be brought back with Undo. INDIRECT, which builds a reference from text, returns #REF! when it points at a workbook that is closed: open that workbook and the formula recalculates.

Practise it: fix a VLOOKUP that shows #REF!

Your task: In F2, return the stock of the code in E2. The formula =VLOOKUP(E2,A2:B5,3,FALSE) shows #REF!: write one that works.

Graded live

F2fx

Row	A	B	C	D	E	F
1	Code	Item	Stock		Find	Stock
2	P-14	Bolts	320		P-09
3	P-09	Nuts	1,150
4	P-22	Washers	80
5	P-03	Screws	640

Write the formula in the bar above, then press Check or Enter.

Hint

Keep column 3, but make the table reach the Stock column: A2:C5. End with FALSE for an exact match.

Solution

=VLOOKUP(E2,A2:C5,3,FALSE) Any formula that returns the same result passes.

Check

Find them all at once

Press Ctrl+F, search for #REF! with Look in set to Formulas, and choose Find All. The list shows every broken formula in the sheet; click one to go to it. Don’t hide #REF! with IFERROR: it always means the formula lost track of its data.