#REF! error – a guide to how to correct this

assorted title books on brown wooden shelf

  • Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel for iPad, Excel for iPhone, Excel for Android tablets, Excel for Android phones, Excel for Windows Phone 10, Excel Mobile, Excel Starter 2010.

This guide will teach you how to rectify the #REF! error and how this can occur. It also presents solution regarding Object Linking and Embedding (OLE) links, Dynamic Data Exchange (DDE) topics, and macro issues.

When you get the #REF! error, it is usually an indicator that a formula makes an invalid cell reference. This frequently occurs any time cells that were formerly referenced by formulas are erased, or pasted with other data.

Example – #REF! error caused by deleting a column

The displayed example applies 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 erase column B, C or D, it would result in a #REF! error. In this scenario, we’ll remove column C (2007 Sales), and the formula now states =SUM(B2,#REF!,C2). Once you employ explicit cell references such as this (where you reference each cell separately, split by a comma) and erase a referenced row or column, Excel is unable to rectify it, thus it yields the #REF! error. This is the main reason why relying on explicit cell references in functions is inadvisable.

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

Solution

  • If you unintentionally removed rows or columns, you can instantly select the Undo button on the Quick Access Toolbar (or press CTRL+Z) to restore them.
  • Modify the formula to make it use a range reference as opposed to individual cells, like =SUM(B2:D2). Following this, you could erase any column inside the sum range and Excel will instantaneously amend the formula. You could even employ =SUM(B2:B5) for a sum of rows.

Example – VLOOKUP with incorrect range references

In the presented example below, =VLOOKUP(A8,A2:D5,5,FALSE) will bring back a #REF! error since it’s searching for a value to yield from column 5. However, the reference range is A:D, which is merely 4 columns instead of 5.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

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

Example – INDEX with incorrect row or column reference causing #REF! error

In this example, the formula =INDEX(B2:E5,5,5) generates a #REF! error since the INDEX range is 4 rows by 4 columns. However, the formula is asking to transfer the contents of 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

Modify the row or column references to reposition them within the INDEX lookup range. =INDEX(B2:E5,4,4) would capture a valid result.

Example – Referencing a closed workbook with INDIRECT leading to a #REF! error

In the following example, an INDIRECT function is endeavouring to reference a closed workbook, resulting in a #REF! error.

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

Solution

Access the referenced workbook. You’ll reach the exact same error if you reference a closed workbook using a dynamic array function.

OLE Issues causing a #REF! error

If you have applied an Object Linking and Embedding (OLE) link that is creating a #REF! error, then access the programme that the link is referencing.

Note: OLE is a technology that you can use to distribute information between programmes.

DDE Issues causing a #REF! error

If you have accessed a Dynamic Data Exchange (DDE) topic that is generating a #REF! error, first identify to ensure that you’re associating the accurate topic. If you’re still getting a #REF! error, review your Trust Centre Settings for external content as described in Block or unblock external content in Office documents.

Note: Dynamic Data Exchange (DDE)is an long-standing protocol for exchanging data between Microsoft Windows-based programmes.

Macro Issues causing a #REF! error

If a macro inputs a function on the worksheet that links to a cell above the function, and the cell that includes the function is in row 1, the function will bring back #REF!. This is due to the fact that there are no cells higher which surpass row 1. Proofread the function to check if an argument is connected to an invalid dataset, like a cell or cell range. This might mandate macro editing in the Visual Basic Editor (VBE) to fully embody that scenario.

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.

See Also

Overview of formulas in Excel

How to avoid broken formulas

Detect errors in formulas

Excel functions (alphabetical)

Excel functions (by category)

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

Leave a Reply

%d bloggers like this: