#REF! means a formula refers to a cell that is not there. The usual cause is a deleted row, column or sheet: when column C is deleted, Excel rewrites =B2*C2 as =B2*#REF!, and the result is #REF! from then on. Press Ctrl+Z (Cmd+Z on a Mac) right after the delete to get the column and the formula back.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Price | Qty | Total |
| 2 | Apple | 1.2 | 10 | #REF! |
| 3 | Pear | 1.5 | 20 | #REF! |
| 4 | Plum | 0.8 | 15 | #REF! |
| 5 | Bread | 2.4 | 5 | #REF! |
#REF! The formula points at a cell that does not exist.The quantity column was deleted and typed back in, but the formula still says #REF!: Excel never repairs a reference once it is gone. Click D2, replace #REF! with C2 and press Enter. The whole column follows, and D2 shows 12.
How #REF! gets into a formula
Excel writes #REF! into a formula whenever a cell the formula used disappears:
| You did this | =B2*C2 in D2 becomes |
|---|---|
| Deleted column C | =B2*#REF! |
| Deleted row 2 | the formula is deleted with its row; formulas in other rows that pointed at row 2 get #REF! |
| Deleted the sheet a formula refers to | =#REF!B2*2 (for a formula like =Prices!B2*2) |
| Cut a cell and pasted it over a cell the formula uses | #REF! in place of the overwritten reference |
Deleting cells inside a range is safe: =SUM(B2:D2) becomes =SUM(B2:C2) when column C is deleted. Deleting the first or last cell of a range only shrinks it. So =SUM(B2:D2) is safer than =B2+C2+D2, which becomes =B2+#REF!+C2.
Why VLOOKUP returns #REF!
VLOOKUP's third argument counts columns inside the table range. If it is larger than the number of columns in the range, the result is #REF!.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Stock | Look for | Stock | |
| 2 | Apple | 1.2 | 40 | Pear | #REF! | |
| 3 | Pear | 1.5 | 25 | |||
| 4 | Plum | 0.8 | 60 | |||
| 5 | Bread | 2.4 | 12 | |||
| 6 | Milk | 1.1 | 30 |
#REF! The formula points at a cell that does not exist.A2:C6 has three columns, so 4 does not exist. Change the 4 to 3 and F2 shows 25. This happens most after deleting a column from the lookup table: the range shrinks, the hard-coded column number does not. XLOOKUP or INDEX with MATCH avoid it because they name the return column directly, as in =XLOOKUP(E2,A2:A6,C2:C6). See VLOOKUP for the rest of its arguments.
#REF! with INDEX and OFFSET
INDEX returns #REF! when the row or column number is outside its range, and OFFSET when it moves above row 1 or before column A.
| A | B | C | |
|---|---|---|---|
| 1 | Score | Result | What it asks for |
| 2 | 88 | #REF! | 6th value of 5 |
| 3 | 72 | 95 | 3rd value of 5 |
| 4 | 95 | #REF! | 2 rows above A2 |
| 5 | 64 | 81 | 4 rows below A2 |
| 6 | 81 |
#REF! The formula points at a cell that does not exist.A2:A6 has five scores, so INDEX(A2:A6,6) is #REF! while INDEX(A2:A6,3) returns 95. Row 0 does not exist, so OFFSET(A2,-2,0) is #REF!, and OFFSET(A2,4,0) lands on A6: 81. When the position comes from another formula (a MATCH, a COUNT), check that formula first. More in the INDEX page.
INDIRECT gives #REF! too when its text is not a valid address (=INDIRECT("ZZZ1"), since the last column is XFD) or points into a workbook that is closed.
#REF! when copying a formula
A relative reference moves with the formula. Copy it far enough up or to the side and the reference falls off the sheet:
C3: =B2*2 (one row up, one column back)
copy C3 to B2: =A1*2
copy C3 to A2: =#REF!*2 (there is no column before A)
The same thing happens when a formula copied to another sheet or workbook points at cells that do not exist there. Lock the cells that must not move with $ (=$B$2*2), or copy the formula text from the formula bar instead of the cell. Absolute references explain the $.
Find and remove every #REF! in a workbook
- Press Ctrl+F (Cmd+F on a Mac), type
#REF!, open Options, set Look in to Formulas, and click Find All. The list shows every formula with a broken reference. - To fix many at once, use Ctrl+H (Control+H on a Mac): find
#REF!, replace with the correct reference, but only when every hit should get the same cell. - Check Formulas > Name Manager: a name whose Refers To column shows
#REF!breaks every formula that uses it. - If the deleted data is gone and the formula is no longer needed, select the cells and replace the formulas with their values (Copy, then Home > Paste > Values). Error values stay errors, so delete those cells afterwards.
Fix a lookup that returns #REF!
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Stock | Look for | Stock | |
| 2 | Apple | 1.2 | 40 | Plum | ||
| 3 | Pear | 1.5 | 25 | |||
| 4 | Plum | 0.8 | 60 | |||
| 5 | Bread | 2.4 | 12 | |||
| 6 | Milk | 1.1 | 30 |
Your turn: =VLOOKUP(E2,A2:C6,4,FALSE) returned #REF!. Write a working lookup in F2 that returns the stock of the product in E2.
Any lookup that returns 60 here and follows the data passes: the VLOOKUP with column 3, =XLOOKUP(E2,A2:A6,C2:C6) or =INDEX(C2:C6,MATCH(E2,A2:A6,0)).
Frequently Asked Questions
What does #REF! mean in Excel?
The formula points at a cell that does not exist. Most often a row, column or sheet the formula used was deleted, and Excel replaced the reference with #REF!, so =B2*C2 became =B2*#REF!. VLOOKUP and INDEX also return #REF! when the column or row number is larger than the range.
How do I fix #REF! after deleting a column?
Press Ctrl+Z (Cmd+Z on a Mac) straight away to undo the delete. If it is too late, click the formula and replace #REF! with the cell it should use, then fill the formula down again.
Why does VLOOKUP return #REF!?
The column number is larger than the number of columns in the table range. =VLOOKUP(E2,A2:C6,4,FALSE) asks for the 4th column of a 3-column range. Use 3, or widen the range to A2:D6.
How do I find all #REF! errors in a workbook?
Press Ctrl+F (Cmd+F on a Mac), search for #REF!, set Look in to Formulas and click Find All. Excel lists every formula that contains a broken reference. Check Formulas > Name Manager too: names can point at #REF! after a delete.
How do I avoid #REF! when deleting rows or columns?
Refer to ranges instead of single cells. =SUM(B2:D2) shrinks to =SUM(B2:C2) when column C or D is deleted, while =B2+C2+D2 turns into =B2+#REF!+C2. Lookups that name their return column, such as =XLOOKUP(E2,A2:A6,C2:C6), survive inserting columns and deleting columns they do not use.
