#REF! Error in Excel: What It Means and How to Fix It
By ExcelReady TeamUpdated 5 min read
The short answer
#REF! means a formula refers to a cell that isn't valid any more. Usually a row, column or sheet it used was deleted, or a copied formula now points off the edge of the sheet. Press Ctrl+Z straight away to undo the deletion. Otherwise, edit the formula and replace each #REF! with the right cell.
Key takeaways
- #REF! means a reference is broken: the cell, row, column or sheet it pointed to is gone.
- Undo (Ctrl+Z or ⌘Z) right after a deletion is the fastest fix.
- Ranges like SUM(B2:D2) shrink when you delete a column inside them; separate references like B2+C2+D2 break.
- VLOOKUP returns #REF! when the column number is larger than the table.
- Don't hide #REF! with IFERROR; it means the formula itself needs repair.
On this page+
- What does the #REF! error mean?
- Cause 1: you deleted a row, column or cell the formula used
- Cause 2: you deleted a sheet the formula used
- Cause 3: a copied formula points off the edge of the sheet
- Cause 4: you pasted over a referenced cell after cutting
- Cause 5: a lookup asks for a column or row that isn't there
- How to find every #REF! error
- Should you use IFERROR on #REF!?
- Practise it
A #REF! error in Excel means a formula refers to a cell that no longer exists. Most of the time a row, column or sheet was deleted, or a formula was copied somewhere its references can't follow. This guide explains each cause, how to repair the formula, and the habits that stop #REF! from appearing in the first place.
What does the #REF! error mean?#
#REF! stands for an invalid reference. A reference is the address a formula uses, like B5 or Prices!B3. When
the cell behind that address is removed, Excel can't point at it any more, so it writes #REF! into the formula
itself and the cell shows the error.
That makes #REF! different from most errors: the problem is in the formula, not in the data. You can see it by
clicking the cell and reading the formula bar, where the broken part literally says #REF!.
Cause 1: you deleted a row, column or cell the formula used#
Deleting is the most common cause. Say E2 adds three months of sales:
=B2+C2+D2Now you delete column C. The formula moves to D2 and becomes:
=B2+#REF!+C2The old C2 is gone, so that piece is broken.
How to fix it:
- If you just deleted the column, press Ctrl+Z (⌘Z on a Mac) to undo. The formula comes back intact.
- If it's too late to undo, click the cell, find
#REF!in the formula bar, and replace it with the right cell, or simply remove it if that data is meant to be gone:=B2+C2.
How to prevent it: use a range. =SUM(B2:D2) doesn't break when you delete a column inside it; the range shrinks
to =SUM(B2:C2). A range only breaks if you delete every cell in it.
Cause 2: you deleted a sheet the formula used#
A formula like =Jan!B5+Feb!B5 depends on both sheets. Delete the Feb sheet, and the formula becomes
=Jan!B5+#REF!B5.
Excel asks you to confirm before it deletes a sheet, because depending on your version, Ctrl+Z may not bring it back. If undo doesn't work and you haven't saved since, close the file without saving and reopen it. Otherwise, rebuild the sheet or point the formula at the data's new location. Renaming a sheet, on the other hand, is safe: Excel updates every formula with the new name. Our guide to referencing another sheet and the worksheets lesson show how cross-sheet references behave.
Cause 3: a copied formula points off the edge of the sheet#
Relative references move when you copy a formula. If they would move above row 1 or left of column A, there's no cell to point to, so Excel writes #REF!.
Example: B3 holds =B2*1.1 (10% more than the row above). Copy B3 and paste it into B1. The reference moves up
two rows, from B2 to "B0", which doesn't exist:
=#REF!*1.1The fix is to paste the formula where its references make sense, or to lock the cell that shouldn't move with dollar
signs, such as $B$2. See how to copy a formula without changing its references
and the copy, cut and paste lesson for how references shift as you paste.
Cause 4: you pasted over a referenced cell after cutting#
Cutting is different from copying. When you cut (Ctrl+X or ⌘X) a cell and paste it on top of a cell other formulas use, those formulas lose their reference and show #REF!. The cell they pointed to was replaced, not moved.
Copying and pasting over a cell doesn't do this; the formulas just see the new value. If you need to rearrange data that formulas depend on, insert new rows or columns and move the data into them, rather than cutting over existing cells. Filling down and right is also safer than cut and paste for extending a formula.
Cause 5: a lookup asks for a column or row that isn't there#
Lookup functions return #REF! when the column or row number is outside the range:
| Formula | Range size | Problem |
|---|---|---|
=VLOOKUP(F2,A2:C6,4,FALSE) | 3 columns | Asks for column 4 |
=INDEX(C2:C6,7) | 5 rows | Asks for row 7 |
=HLOOKUP(F2,A1:E3,5,FALSE) | 3 rows | Asks for row 5 |
For the VLOOKUP, count the columns in the table range, not on the sheet. Price is the 3rd column of A2:C6:
=VLOOKUP(F2,A2:C6,3,FALSE)This kind of #REF! often appears after someone deletes a column from a lookup table. XLOOKUP and INDEX MATCH avoid it because they point at the return column directly instead of counting.
How to find every #REF! error#
In a big workbook, search for the broken references instead of scrolling:
- Press Ctrl+F (⌘F on a Mac) to open Find.
- Type
#REF!. - Click Options and set Look in to Formulas.
- Click Find All. Excel lists every cell whose formula contains
#REF!.
Fix them one by one. Formulas > Trace Precedents (on the Formula Auditing group) helps you see what a repaired formula now depends on.
Should you use IFERROR on #REF!?#
No. IFERROR would replace the error with a blank or a 0, but the formula would still be broken, and you'd lose the warning. #REF! always means "repair this formula". Keep IFERROR for errors that come from the data, like the expected #DIV/0! error when a cell is still 0. If a function name is the problem instead, see the #NAME? error.
Practise it#
Fix the broken lookup in the exercise on this page. Then use the copy, cut and paste lesson to see exactly how references move, and the worksheets and cross-sheet references lesson to practise formulas that span sheets.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
The price list in A2:C6 has three columns, but the formula someone wrote in G2, =VLOOKUP(F2,A2:C6,4,FALSE), asks for column 4 and returns #REF!. Fix it in G2 so it returns the price, the 3rd column.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Price | Price | ||||||
| 2 | P-103 | |||||||
| 3 | ||||||||
| 4 | ||||||||
| 5 | ||||||||
| 6 | ||||||||
| 7 | ||||||||
| 8 | ||||||||
| 9 |
Tip: Count the columns of A2:C6: Code is 1, Product is 2, Price is 3.
Practise this in a lesson
Frequently asked questions
What does #REF! mean in Excel?
It means "invalid reference": the formula points to a cell that no longer exists, usually because a row, column or sheet was deleted.
How do I fix #REF! in Excel?
If you just deleted something, press Ctrl+Z (⌘Z on a Mac) to undo it. Otherwise, click the cell, find #REF! in the formula bar and replace it with the correct cell or range.
Why does VLOOKUP return #REF!?
The column number is bigger than the number of columns in the table range. For a table of A2:C6, the column number can only be 1, 2 or 3.
How do I find all #REF! errors in a workbook?
Press Ctrl+F (⌘F on a Mac), type #REF!, set Look in to Formulas and click Find All. The list shows every cell whose formula contains a broken reference.
Can I stop #REF! errors from happening?
Use ranges such as SUM(B2:D2) instead of B2+C2+D2, avoid deleting rows other formulas use, and look up columns with XLOOKUP or INDEX MATCH instead of a fixed column number.