#REF! in Excel: what it means and how to fix it
#REF! means a formula refers to a cell that no longer exists, usually because you deleted a row, column or sheet.
Try the first exercises for freeIn the lesson Understanding error messages
What does #REF! mean?
#REF! means that a formula refers to a cell that no longer exists. Usually you've deleted a row, a column or a worksheet that the formula was looking at.
Excel can't guess which cell you meant, so it puts #REF! in the formula itself, in place of the old reference. VLOOKUP with a column number larger than your table also gives this error.
When do you get #REF!?
- You delete column B, while a formula was using =A1+B1.
- You delete a worksheet that formulas refer to.
- You copy a formula so far to the left or up that the reference would fall outside the worksheet.
- VLOOKUP asks for column 4 from a table of three columns.
How to fix #REF!, step by step
This is how to fix the error:
- Did you just delete something? Press Ctrl + Z straight away: then everything is back.
- Otherwise, click the cell and look in the formula bar to see where #REF! is.
- Replace #REF! with the right cell or the right range.
- From now on, use ranges, such as =SUM(A1:C1), rather than separate cells with plus signs: a range adjusts if you delete a column in the middle.
Examples
A column number outside the table
E1 contains =VLOOKUP("P-204",A2:C5,4,FALSE). The table A2:C5 only has three columns, so column 4 doesn't exist.
Fixed
E1 contains =VLOOKUP("P-204",A2:C5,3,FALSE). With column 3 you get the price.
Where it often goes wrong
- With a range, such as =SUM(A1:C1), Excel adjusts the range when you delete a column in the middle. Separate cells joined with plus signs don't have that protection.
- VLOOKUP with a column number larger than your table also gives #REF!.
Practise this in the lesson Understanding error messages. The first exercises are free.
Frequently asked questions
Can I hide #REF! with IFERROR?
You can, but then you're hiding a broken formula. It's better to repair the reference.