Excel errors / #REF!
#REF! error in Excel
The formula refers to a cell that no longer exists.
#REF! means a formula points at a cell that is not there. Nearly always, the cell was there once: a row, a column or a whole sheet was deleted, and the formulas that used it were left pointing at nothing.
Excel writes the damage into the formula itself. =A2+B2 becomes =A2+#REF!, and the lost reference cannot be recovered from the formula. That is why the first fix is always Undo.
Have a formula that shows #REF!? Paste it into the formula checker with a few rows of your data. It points at the part that fails and says why.
4 causes of #REF!, and how to fix each
A row, column or sheet was deleted
After column B is deleted, a formula that added A2 and B2 has nothing left for its second half. Excel replaces the reference with #REF! inside the formula.
A B C D 1 Jan Feb 2 120 95 #REF! In D2:
=A2+#REF!→ #REF!How to fix it. Press Ctrl+Z straight away to bring the deleted cells back. If it is too late for that, retype the reference. To remove a column you no longer need, clear its contents or hide it; do not delete it while formulas use it.
A B C D 1 Jan Feb 2 120 95 215 In D2:
=A2+B2→ 215VLOOKUP asks for a column the table does not have
The third argument of VLOOKUP is a column number counted from the left of the table. If the table is two columns wide and the formula asks for column 3, there is nothing to return.
A B C D 1 Code Price 2 P-1 3.5 #REF! 3 P-2 1.2 In D2:
=VLOOKUP("P-1", A2:B3, 3, FALSE)→ #REF!How to fix it. Count the columns of the table range, not of the sheet. Or widen the range so that it includes the column you want.
A B C D 1 Code Price 2 P-1 3.5 3.5 3 P-2 1.2 In D2:
=VLOOKUP("P-1", A2:B3, 2, FALSE)→ 3.5INDEX is asked for a position outside its range
INDEX returns the item at a position you give it. Position 5 in a list of two is outside the range.
A B C 1 Name 2 Ana #REF! 3 Ben In C2:
=INDEX(A2:A3, 5)→ #REF!How to fix it. Check where the position comes from. With MATCH, make sure it searches a range of the same size as the one INDEX returns from.
A B C 1 Name 2 Ana Ben 3 Ben In C2:
=INDEX(A2:A3, 2)→ BenA copied formula points off the edge of the sheet
A formula in B5 that refers to B1 means "four rows up". Copied to B2, four rows up would be above row 1, which does not exist, so the copy shows #REF!.
How to fix it. Lock the reference with $ signs (
$B$1) before copying, so it keeps pointing at the same cell wherever the formula goes.
Hiding it on purpose
Do not hide #REF! with IFERROR. Unlike #N/A, it is never an expected result: it means the formula has lost part of itself and its answer can no longer be trusted.
Common questions
How do I fix #REF! in Excel?
If you have just deleted something, press Ctrl+Z. Otherwise click the cell, find #REF! in the formula bar, and replace it with the cell or range it should point at. The formula cannot tell you what was there, so you may need an older copy of the file.
How do I find every #REF! error in a workbook?
Press Ctrl+F, type #REF!, set "Look in" to Formulas and "Within" to Workbook, and choose Find All. Or use Home > Find & Select > Go To Special > Formulas with only Errors ticked, which selects every error on the sheet.
How do I stop #REF! from happening?
Refer to whole ranges, such as =SUM(B2:D2), instead of single cells joined with +. Deleting a column inside a range just makes the range smaller; deleting a cell a formula names directly breaks it.
Practise it
Functions involved
- VLOOKUP: Finds a value in the first column of a table and returns a value from the same row.
- INDEX: The value at a given row and column of a range.
- XLOOKUP: Searches a range and returns the matching item from another range. The modern replacement for VLOOKUP.
- MATCH: The position of a value in a row or column.
- SUM: Adds up numbers, cells and ranges.
Other errors
- #N/A: A lookup could not find the value it was given.
- #VALUE!: The formula was given the wrong kind of value, such as text where it needs a number.
- #DIV/0!: The formula divides by zero or by an empty cell.
- #NAME?: Excel does not recognise a word in the formula.
- #NUM!: The calculation has no valid number as its answer.
- #SPILL!: A formula that returns several values has no room to put them.
- #CALC!: A formula that returns a list ended up with nothing to return.
- #NULL!: Two ranges that were expected to cross do not.
- #####: The column is too narrow to show the number, or a date is negative.
- Circular reference: A formula depends on its own result.