Excel practice › Excel errors › #REF! error

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

#REF! means the formula points at something that doesn't exist: a cell that was deleted, or a position outside a range, like the fifth column of a four-column table. If it appeared right after you deleted a row, a column or a sheet, press Ctrl+Z. The reference comes back with the cells.

In a lookup, check the number that picks the column. =VLOOKUP(F2,A2:D7,5,FALSE) asks for a fifth column that A2:D7 doesn't have. If the value you want is in the fourth: =VLOOKUP(F2,A2:D7,4,FALSE).

Skip to the 3 exercises ↓

The causes, in the order worth checking

  1. A row, a column or a sheet the formula used was deleted, or other cells were pasted over the ones it used.
  2. A VLOOKUP column number bigger than the table.
  3. INDEX asked for a row or a column outside its range.
  4. A link to another workbook that Excel can't follow.

1. Something the formula used was deleted

This one can't be reproduced in a sheet on this page, so here is what happens in Excel. Say E2 adds three cells one by one, and someone deletes column C:

BeforeAfter deleting column C
=SUM(B2,C2,D2)=SUM(B2,#REF!,C2)

Excel rewrote the formula. D2 moved left and became C2, and the cell that is gone became #REF!, inside the formula itself. That's why putting the column back by hand doesn't repair it: the formula no longer says where the cell was.

To make formulas survive deletions, give functions a range. =SUM(B2:D2) shrinks to =SUM(B2:C2) when a column inside it is deleted, and keeps working. Before deleting data that formulas depend on, Microsoft's advice is to replace those formulas with their results first (copy, then Paste Special, Values).

2. A VLOOKUP column number bigger than the table

Deleting a column inside a lookup table doesn't always leave a #REF! in the formula text. The table range shrinks correctly, and the column number, which is a plain number, stays as it was. This table had five columns until Region was deleted. The formula still asks for the fifth:

Live example#REF!: column 5 of a 4-column table
G2fx
ABCDEFG
1StoreCityManagerRentStoreRent
211AustinR. Patel840022#REF!
314BoiseJ. Kim5200
417DenverA. Flores9100
522OmahaS. Wright4700
625TampaM. Chen7600
731TulsaD. Novak4300

The same error appears when the column number is right and the range is too narrow: =VLOOKUP(F2,A2:C7,4,FALSE) has the right 4 and a table that stops at column C.

3. INDEX asked for a row or a column outside its range

INDEX returns the cell at a position inside a range, and #REF! when the position is past the end. With INDEX/MATCH it happens when the two functions are given ranges of different heights:

Live example#REF!: the 6th cell of a 4-cell range
G2fx
ABCDEFG
1StoreCityManagerRentStoreManager
211AustinR. Patel840031#REF!
314BoiseJ. Kim5200
417DenverA. Flores9100
522OmahaS. Wright4700
625TampaM. Chen7600
731TulsaD. Novak4300

4. A link to another workbook that Excel can't follow

Two cases from Microsoft's documentation. A reference to another workbook needs the file name in square brackets, as in =[Budget.xlsx]Sales!A1:A8. Without the brackets the formula shows #REF!. And INDIRECT pointing at a workbook that is closed returns #REF! until that workbook is opened.

Practice: 3 formulas to fix

Type the formula in the box and press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data.

Exercise 1After a column was deleted

The table had a Region column until someone deleted it. It's four columns wide now, and =VLOOKUP(F2,A2:D7,5,FALSE) shows #REF!. In G2, write the formula that returns the rent for the store in F2.

G2fx
ABCDEFG
1StoreCityManagerRentStoreRent
211AustinR. Patel840017
314BoiseJ. Kim5200
417DenverA. Flores9100
522OmahaS. Wright4700
625TampaM. Chen7600
731TulsaD. Novak4300
Show the answer

=VLOOKUP(F2,A2:D7,4,FALSE) gives 9100.

Rent is the 4th column of A2:D7 since Region was deleted. The range adjusted by itself; the column number never does.

Exercise 2INDEX and MATCH on different ranges

=INDEX(C2:C5,MATCH(F2,A2:A7,0)) should return the manager of the store in F2. For store 25 it shows #REF!. In G2, write the formula that works for every store.

G2fx
ABCDEFG
1StoreCityManagerRentStoreManager
211AustinR. Patel840025
314BoiseJ. Kim5200
417DenverA. Flores9100
522OmahaS. Wright4700
625TampaM. Chen7600
731TulsaD. Novak4300
Show the answer

=INDEX(C2:C7,MATCH(F2,A2:A7,0)) gives M. Chen.

MATCH returns a position in A2:A7, and INDEX reads the same position in C2:C7. The two ranges cover the same rows, so every position exists.

Exercise 3A column number past the range

=INDEX(B2:D7,MATCH(F2,A2:A7,0),4) should return the rent for the store in F2. It shows #REF!. In G2, write the formula that works.

G2fx
ABCDEFG
1StoreCityManagerRentStoreRent
211AustinR. Patel840014
314BoiseJ. Kim5200
417DenverA. Flores9100
522OmahaS. Wright4700
625TampaM. Chen7600
731TulsaD. Novak4300
Show the answer

=INDEX(B2:D7,MATCH(F2,A2:A7,0),3) gives 5200.

The column number counts inside the range given to INDEX. B2:D7 starts at City, so Rent is its 3rd column.

Questions people ask

What does #REF! mean in Excel?

"Reference": the formula refers to a cell, a range or a position that isn't valid. Either the cells were deleted or pasted over, or a function was asked for a column or a row beyond the range it was given.

How do I fix a #REF! error after deleting a column?

Press Ctrl+Z right away: the column and the references come back together. If undo is no longer possible, open each formula and replace the #REF! inside it with the correct reference. Putting the column back by hand doesn't repair the formulas.

Why does VLOOKUP return #REF!?

Its third argument is bigger than the number of columns in the table range. =VLOOKUP(F2,A2:D7,5,FALSE) asks for column 5 of a range that has four. Lower the number or widen the range.

How do I avoid #REF! errors?

Give functions ranges, not lists of single cells: =SUM(B2:D2) adjusts when a column inside it is deleted, and =SUM(B2,C2,D2) breaks. Before deleting rows, columns or sheets, check whether formulas use them, and if you only need their results, paste them as values first.

Keep going

Ten questions in a real spreadsheet, from SUM to INDEX/MATCH, with a level at the end and the lesson to start from. Free, no sign-up.

Related

VLOOKUP not working: causes and fixes#N/A error in Excel: meaning and fixesVLOOKUP function, with live examplesINDEX MATCH, with live examplesXLOOKUP function, with live examplesINDEX MATCH practice exercises (Pro)Excel formula errors, fixed liveExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: How to correct a #REF! error, Microsoft Support: How to avoid broken formulas in Excel, Microsoft Support: VLOOKUP function. Last checked November 2026.