- 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.
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.
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.
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.
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.
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
Excel functions (alphabetical)
Other Links
Back to Software Bytes – Official Website