#REF! error: what it means and how to fix it
The formula points at a cell that isn't valid any more.
#REF! means a reference is broken. Usually cells the formula used were deleted (or pasted over), or a lookup asks for a column or row outside its range. Once a reference turns into #REF! inside the formula, it can't fix itself.
Common causes, ranked
- 1
Cells, rows or columns the formula used were deleted
=SUM(B2,C2,D2)loses a reference when column C is deleted: it becomes=SUM(B2,#REF!,C2).Fix: Undo (Ctrl+Z) straight away if you can. Otherwise rewrite the formula. Ranges survive deletions better than separate cells:
=SUM(B2:D2)just shrinks.Shows the error
=SUM(B2,C2,D2)Fixed
=SUM(B2:D2) - 2
A VLOOKUP column number bigger than the table
Asking for column 5 of a 4-column table gives
#REF!.Fix: Widen the table range or use a smaller column number.
Shows the error
=VLOOKUP(A8,A2:D5,5,FALSE)Fixed
=VLOOKUP(A8,A2:E5,5,FALSE) - 3
INDEX asked for a row or column outside its range
=INDEX(B2:E5,5,5)asks for row 5, column 5 of a 4 × 4 range.Fix: Use a row and column inside the range.
Shows the error
=INDEX(B2:E5,5,5)Fixed
=INDEX(B2:E5,4,4) - 4
A link to another workbook that's closed
In Excel,
INDIRECTpointing at a closed workbook gives#REF!.Fix: Open the other workbook.
See it live
Edit the formula in D2 and fix the error yourself.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | SKU | Qty | Product | SKU | Product | Price | Stock | ||
| 2 | 2001 | PL-104 | 2 | #REF! | PL-101 | Fern | $9.50 | 40 | ||
| 3 | 2002 | PT-210 | 1 | #REF! | PL-104 | Monstera | $34.00 | 12 | ||
| 4 | 2003 | TL-301 | 3 | #REF! | PL-107 | Snake plant | $22.00 | 25 | ||
| 5 | 2004 | PL-101 | 1 | #REF! | PT-210 | Terracotta pot | $12.50 | 60 | ||
| 6 | 2005 | SL-401 | 4 | #REF! | PT-212 | Ceramic pot | $19.00 | 18 | ||
| 7 | 2006 | PL-104 | 1 | #REF! | TL-301 | Watering can | $18.00 | 9 | ||
| 8 | 2007 | PT-212 | 2 | #REF! | SL-401 | Potting mix | $7.25 | 80 | ||
| 9 | 2008 | PL-107 | 1 | #REF! |
Should you hide it?
Don't hide #REF!. It means the formula is broken, and a hidden broken formula keeps giving wrong totals.
Careful: Wrapping a #REF! in IFERROR turns a loud, fixable problem into a silent wrong answer. Find the reference that broke and rewrite it.
Excel vs Google Sheets
#REF! is an error value in both Excel and Google Sheets.
Sources
Practise this in 5 minutes
Fixing #REF! is taught in Lesson 15: VLOOKUP (Unit 4, Lookups), on a live sheet with instant feedback. Pro
Open “VLOOKUP”Need a quick reference? The formula cheat sheet is free to read and print.