Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel for iPad Excel for iPhone Excel for Android tablets Excel for Android phones Excel for Windows Phone 10 Excel Mobile More. Less
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.
#REF! error caused by deleting a columnThe following example uses the formula =SUM(B2,C2,D2) in column E.
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.
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.
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." />
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).
INDEX with incorrect row or column referenceIn 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.
Adjust the row or column references so they're inside the INDEX lookup range. =INDEX(B2:E5,4,4) would return a valid result.
Referencing a closed workbook with INDIRECTIn the following example, an INDIRECT function is attempting to reference a workbook that’s closed, causing a #REF! error.
Open the referenced workbook. You'll encounter the same error if you reference a closed workbook with a dynamic array function.
Structured references not supportedStructured references to table and column names in linked workbooks aren’t supported.
Calculated references not supportedCalculated references to linked workbooks aren’t supported.
Invalid Cell Reference ErrorMoving or deleting cells caused an invalid cell reference, or function is returning reference error.
OLE issuesIf 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.
DDE issuesIf you have used a Dynamic Data Exchange (DDE) topic that is returning a #REF! error, first check to make sure you’re referencing the correct topic. If you're still receiving a #REF! error, check your Trust Center Settings for external content as outlined in Block or unblock external content in Microsoft 365 documents.
Note: Dynamic Data Exchange (DDE) is an established protocol for exchanging data between Microsoft Windows-based programs.
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.
You can always ask an expert in the Excel Tech Community or get support in Communities.