The #REF! guide
#REF! means a formula points at something that doesn't exist: a column past the edge of a lookup table, or a cell that was deleted. When a cell is deleted, Excel writes #REF! into the formula in its place.
A lookup past the edge
=VLOOKUP(A2, $F$2:$H$8, 4, FALSE) asks for the 4th column of a table that has 3. There's no 4th column, so every row shows #REF!. Count the columns from the first column of the table, not from column A: F is 1, G is 2, H is 3. INDEX gives #REF! the same way when the row or column number is outside its range.
A deleted row or column
A total written as =D2+D3+D4+D5+D6+D7 lists every cell. Delete row 4 and Excel can't point at that cell any more, so the formula becomes =D2+D3+#REF!+D4+D5+D6, and the result is #REF!. The #REF! stays in the formula: undo straight away with Ctrl+Z, or rewrite the formula.
A range doesn't break this way. =SUM(D2:D7) becomes =SUM(D2:D6) when a row inside it is deleted. Only deleting every row of a range gives SUM(#REF!).
How to fix it
- Click the cell and read the formula bar. Look for #REF! written into it, or a column number in a lookup.
- For a lookup: lower the column number, or widen the table to include the column you need.
- For a deleted cell: replace the list of cells with a range,
=SUM(D2:D6).
Common mistakes
- Taking out the #REF! only.
=D2+D3+D4+D5+D6works today, and breaks the next time a line is deleted. - The wrong column number. Column 2 of the table returns the product name, not the price.
- IFERROR around it. #REF! means the formula is broken; hiding it hides the broken total.
XLOOKUP avoids the column count: =XLOOKUP(A2, $F$2:$F$8, $H$2:$H$8) points at the price column directly. See XLOOKUP. Other errors: #DIV/0!, #VALUE!, circular references.
FAQ
- What does #REF! mean in Excel?
- The formula refers to a cell that isn't valid: one that was deleted, or a position outside a range, such as column 4 of a 3-column VLOOKUP table.
- Why does VLOOKUP return #REF!?
- The column number is larger than the number of columns in the table range. Count the columns from the table's first column.
- How do I fix #REF! after deleting a row?
- Undo the delete if you can. Otherwise rewrite the formula without the #REF!, preferably with a range like SUM(D2:D6), which shrinks instead of breaking when a row inside it goes.
- Can I get the deleted reference back?
- Only with Undo. Once the formula says #REF!, it no longer knows which cell it pointed at.



































































