#REF! error in Excel
A formula points at a cell that no longer exists, or at a position outside the range it was given.
What #REF! means
#REF! means a reference is invalid. Unlike #N/A, it never means “no answer”: it means the formula itself is broken or asks for something that cannot exist.
The classic cause is deleting. A formula remembers the cells it points to, and when one of those cells is deleted Excel cannot keep the link, so it replaces the address with #REF! right inside the formula text. You will see it in the formula bar as =#REF!*B2 or =SUM(A1:#REF!). Once the workbook is saved and reopened, the original address is gone.
The other two causes are position errors. VLOOKUP and INDEX both count positions inside a range, and asking for a position beyond its edge returns #REF!. These are mistakes in the arguments rather than in the layout, and they show up the first time the formula runs rather than after an edit.
Causes of #REF! and how to fix each
1. A cell the formula used was deleted
Delete a row or column that a formula refers to and Excel has nothing left to point at, so it rewrites the reference as #REF! inside the formula itself. Suppose =B2*C2 sat in D2 and column B was deleted: the formula moves to C2 and becomes =#REF!*B2, because its second reference slid one column left. The grid below shows that broken formula.
| A | B | C | |
|---|---|---|---|
| 1 | Item | Price | |
| 2 | Pen | 2 | #REF! |
| 3 | Pad | 3 |
Fix. Press Ctrl+Z straight away: undo restores the column and the formula together. If you have already saved and closed, edit the formula and retype the reference by clicking the right cell. To remove data without breaking formulas, clear the contents (Delete key) instead of deleting the row or column.
2. VLOOKUP column number is larger than the table
The third argument of VLOOKUP counts columns from the left edge of the table array. Ask for column 3 of a two-column table and Excel has no such column to return.
| A | B | C | |
|---|---|---|---|
| 1 | ID | Dept | |
| 2 | 1001 | Sales | |
| 3 | 1002 | Ops | |
| 4 | 1002 | #REF! |
Fix. Count from the first column of the table array you typed, not from column A of the sheet. If you widen the table to reach more columns, update the column number to match.
| A | B | C | |
|---|---|---|---|
| 1 | ID | Dept | |
| 2 | 1001 | Sales | |
| 3 | 1002 | Ops | |
| 4 | 1002 | Ops |
Column 2 of A2:B3 is the department, so 1002 returns Ops.
3. INDEX asked for a row or column outside the range
INDEX counts positions inside the range you give it. A range with three rows has no row 5.
| A | B | |
|---|---|---|
| 1 | Name | |
| 2 | Ana | |
| 3 | Kim | |
| 4 | Raj | |
| 5 | 5 | #REF! |
Fix. Check the position against the size of the range before using it, or let MATCH produce the position so it can never overshoot. If the position comes from user input, a guard such as IF(A5<=ROWS(A2:A4),INDEX(A2:A4,A5),"none") keeps the formula honest.
Trapping #REF! in a formula
A #REF! caused by a deleted cell is a broken formula, not a missing answer, so wrapping it in IFERROR only hides the damage. Fix the reference. For positions that can legitimately fall outside a range, test the position first and return a message when it does.
| A | B | |
|---|---|---|
| 1 | Name | |
| 2 | Ana | |
| 3 | Kim | |
| 4 | Raj | |
| 5 | 5 | none |
ROWS(A2:A4) is 3, and the position in A5 is 5, so the guard returns the message instead of an error.
#REF! on Excel job tests
If a prompt hands you a workbook where a formula already shows #REF! and asks you to repair it, the fix is to rebuild the reference (click the cell you meant), not to type a constant over the formula.
Another version asks for a lookup formula and checks it still works after rows are inserted. Referring to whole columns or using a Table keeps ranges from breaking when the layout changes.
Related guides
#REF! questions
- What does #REF! mean in Excel?
- The formula has an invalid reference: the cell was deleted, the range is outside the sheet, or a lookup asked for a position that does not exist.
- Can I recover a formula that shows #REF!?
- Right after the deletion, press Ctrl+Z. Later, you have to edit the formula and re-select the cell you meant, because Excel no longer remembers the original address.
- How do I find every #REF! in a workbook?
- Press Ctrl+F, search for
#REF!, set Look in to Formulas, and choose Within: Workbook. Find All lists every formula that contains it. - Why does copying a formula give #REF!?
- A relative reference can move off the sheet. Copy a formula that points one row up from row 1 and the copy has no row above to point at.
Practice working with errors
The free 5-item sampler uses the same kind of formulas in a working grid, and it shows you the error value when a formula goes wrong.
Try a 5-item Excel test sample