formulastreak

#REF! error: what it means and how to fix it

#REF!

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. 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. 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. 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. 4

    A link to another workbook that's closed

    In Excel, INDIRECT pointing at a closed workbook gives #REF!.

    Fix: Open the other workbook.

See it live

Edit the formula in D2 and fix the error yourself.

VLOOKUP asks for column 5 of a 4-column table: #REF! · edit D2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultD2 = #REF!
ABCDEFGHIJ
1OrderSKUQtyProductSKUProductPriceStock
22001PL-1042#REF!PL-101Fern$9.5040
32002PT-2101#REF!PL-104Monstera$34.0012
42003TL-3013#REF!PL-107Snake plant$22.0025
52004PL-1011#REF!PT-210Terracotta pot$12.5060
62005SL-4014#REF!PT-212Ceramic pot$19.0018
72006PL-1041#REF!TL-301Watering can$18.009
82007PT-2122#REF!SL-401Potting mix$7.2580
92008PL-1071#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”

Get the formulastreak newsletter

Bite-sized formula tips you can use at work the next day, plus a heads-up when new free tools and lessons land. It arrives now and then, never in a flood.

No spam, unsubscribe in one click. We store your email, this consent and when you gave it. Details in our privacy policy.

Need a quick reference? The formula cheat sheet is free to read and print.