How to correct a #REF! error

The #REF! error shows when a formula refers to a cell that's not valid . This happens most often when cells that were referenced by formulas get deleted, or pasted over.

Example - #REF! error caused by deleting a column

The following example uses the formula =SUM(B2,C2,D2) in column E.

A formula using explicit cell references like =SUM(B2,C2,D2) can cause a #REF! error if a column is deleted.

If you were to delete column B, C or D it would cause a #REF! error. In this case we'll delete column C (2007 Sales), and the formula now reads =SUM(B2,#REF!,C2). When you use explicit cell references like this (where you reference each cell individually, separated by a comma) and delete a referenced row or column, Excel can't resolve it, so it returns the #REF! error. This is the primary reason why using explicit cell references in functions is not recommended.

Example of the #REF! error caused by deleting a column.

Solution

If you accidentally deleted rows or columns, you can immediately click the Undo button on the Quick Access Toolbar (or press CTRL+Z) to restore them.
Adjust the formula so that it uses a range reference instead of individual cells, like =SUM(B2:D2). Now you could delete any column within the sum range and Excel will automatically adjust the formula. You could also use =SUM(B2:B5) for a sum of rows.

Example - VLOOKUP with incorrect range references

In the following example, =VLOOKUP(A8,A2:D5,5,FALSE) will return a #REF! error because it's looking for a value to return from column 5, but the reference range is A:D, which is only 4 columns.

Example of a VLOOKUP formula with an incorrect range. Formula is =VLOOKU(A8,A2:D5,5,FALSE). There is no fifth column in the VLOOKUP range, so 5 causes a #REF! error.

Solution

Adjust the range to be larger, or reduce the column lookup value to match the reference range. =VLOOKUP(A8,A2:E5,5,FALSE) would be a valid reference range, as would =VLOOKUP(A8,A2:D5,4,FALSE).

Example - INDEX with incorrect row or column reference

In this example, the formula =INDEX(B2:E5,5,5) returns a #REF! error because the INDEX range is 4 rows by 4 columns, but the formula is asking to return what's in the 5th row and 5th column.

Example of an INDEX formula with an invalid range reference. Formula is =INDEX(B2:E5,5,5), but the range is only 4 rows by 4 columns.

Solution

Adjust the row or column references so they're inside the INDEX lookup range. =INDEX(B2:E5,4,4) would return a valid result.

Example – Referencing a closed workbook with INDIRECT

In the following example, an INDIRECT function is attempting to reference a workbook that's closed, causing a #REF! error.

Example of a #REF! error caused by INDIRECT referencing a closed workbook.

Solution

Open the referenced workbook

OLE/DDE Issues

If you have used an Object Linking and Embedding (OLE) link that is returning a #REF! error, then start the program that the link is calling.

Note: OLE is a technology that you can use to share information between programs.

If you have used a Dynamic Data Exchange (DDE) topic that is returning a #REF! error, make sure you're referencing the correct topic.

Note: DDE is an established protocol for exchanging data between Microsoft Windows-based programs.

Macro Issues

If a macro enters a function on the worksheet that refers to a cell above the function, and the cell that contains the function is in row 1, the function will return #REF! because there are no cells above row 1. Check the function to see if an argument refers to a cell or range of cells that is not valid. This may require editing the macro in the Visual Basic Editor (VBE) to take that situation into account.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Microsoft Office Tutorials

Thursday, March 1, 2018