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

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

    ABCD
    1JanFeb
    212095#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.

    ABCD
    1JanFeb
    212095215

    In D2: =A2+B2 → 215

  2. VLOOKUP 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.

    ABCD
    1CodePrice
    2P-13.5#REF!
    3P-21.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.

    ABCD
    1CodePrice
    2P-13.53.5
    3P-21.2

    In D2: =VLOOKUP("P-1", A2:B3, 2, FALSE) → 3.5

  3. INDEX 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.

    ABC
    1Name
    2Ana#REF!
    3Ben

    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.

    ABC
    1Name
    2AnaBen
    3Ben

    In C2: =INDEX(A2:A3, 2) → Ben

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

Other errors