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).
The causes, in the order worth checking
- A row, a column or a sheet the formula used was deleted, or other cells were pasted over the ones it used.
- A VLOOKUP column number bigger than the table.
- INDEX asked for a row or a column outside its range.
- 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:
| Before | After 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.
- It happened a moment ago: Ctrl+Z, before doing anything else.
- Too late for undo: click the cell, and in the formula bar replace each
#REF!with the reference that belongs there. - A deleted sheet can't be brought back with undo. Close the file without saving if you still can.
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:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Store | City | Manager | Rent | Store | Rent | |
| 2 | 11 | Austin | R. Patel | 8400 | 22 | #REF! | |
| 3 | 14 | Boise | J. Kim | 5200 | |||
| 4 | 17 | Denver | A. Flores | 9100 | |||
| 5 | 22 | Omaha | S. Wright | 4700 | |||
| 6 | 25 | Tampa | M. Chen | 7600 | |||
| 7 | 31 | Tulsa | D. Novak | 4300 |
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:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Store | City | Manager | Rent | Store | Manager | |
| 2 | 11 | Austin | R. Patel | 8400 | 31 | #REF! | |
| 3 | 14 | Boise | J. Kim | 5200 | |||
| 4 | 17 | Denver | A. Flores | 9100 | |||
| 5 | 22 | Omaha | S. Wright | 4700 | |||
| 6 | 25 | Tampa | M. Chen | 7600 | |||
| 7 | 31 | Tulsa | D. Novak | 4300 |
=INDEX(C2:C7,MATCH(F2,A2:A7,0)) returns D. Novak.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.
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.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Store | City | Manager | Rent | Store | Rent | |
| 2 | 11 | Austin | R. Patel | 8400 | 17 | ||
| 3 | 14 | Boise | J. Kim | 5200 | |||
| 4 | 17 | Denver | A. Flores | 9100 | |||
| 5 | 22 | Omaha | S. Wright | 4700 | |||
| 6 | 25 | Tampa | M. Chen | 7600 | |||
| 7 | 31 | Tulsa | D. Novak | 4300 |
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.
=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.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Store | City | Manager | Rent | Store | Manager | |
| 2 | 11 | Austin | R. Patel | 8400 | 25 | ||
| 3 | 14 | Boise | J. Kim | 5200 | |||
| 4 | 17 | Denver | A. Flores | 9100 | |||
| 5 | 22 | Omaha | S. Wright | 4700 | |||
| 6 | 25 | Tampa | M. Chen | 7600 | |||
| 7 | 31 | Tulsa | D. Novak | 4300 |
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.
=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.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Store | City | Manager | Rent | Store | Rent | |
| 2 | 11 | Austin | R. Patel | 8400 | 14 | ||
| 3 | 14 | Boise | J. Kim | 5200 | |||
| 4 | 17 | Denver | A. Flores | 9100 | |||
| 5 | 22 | Omaha | S. Wright | 4700 | |||
| 6 | 25 | Tampa | M. Chen | 7600 | |||
| 7 | 31 | Tulsa | D. Novak | 4300 |
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.