An error in Excel called “#REF!” (with REF standing for reference) happens when a formula refers to an invalid cell. This usually occurs when cells referenced by formulas are deleted or pasted over.
To start addressing the #REF! error, it’s important to understand what causes it in the first place.
This tutorial describes six examples of common causes of the #REF! Error and practical solutions to fix it.
Understanding the #REF! Error in Excel
The #REF! error in Excel is one of the most common errors you may encounter while working with formulas.
It occurs when a formula refers to a cell that is no longer valid. This can happen if the referenced cells are deleted or if you have used an incorrect range reference in the formulas.
This is why the error is called #REF! (short for Reference), which tells us that there is an issue with the referencing in the formulas.
To avoid the #REF! error, it’s essential to be mindful of how you change your worksheet. When deleting or moving cells, consider how these changes will affect the formulas that reference them.
If you must delete or move cells, ensure you update the formulas to account for the changes.
By understanding the causes and potential solutions for the #REF! error in Excel, you can ensure your worksheets remain accurate and reliable, allowing you to work confidently and efficiently.
Also read: #NAME? Error in Excel – How to Fix!
Method #1: #REF! Error Caused by Deleting a Column or Row
Assume we have the following dataset containing a formula =SUM(B2,C2,D2,E2) with explicit cell references (each cell referenced individually separated by a comma) in column F.
Suppose we delete columns B, C, D, or E, the #REF! error occurs. If, for example, we delete column E (Qtr 4 Sales), the formula becomes =SUM(B2,C2,D2,#REF!) as shown below:
It is, therefore, not recommended to use explicit cell references in functions as in the example above because if you reference each cell individually and delete a referenced row or column, Excel will not be able to resolve it, resulting in the #REF! error.
How to Fix
- If you mistakenly deleted rows or columns, you can quickly restore them by clicking the “Undo” button on the “Quick Access Toolbar” or using the “CTRL+Z” shortcut.
- Utilize a range reference instead of individual cells. For instance, you can use =SUM(B2:E2) as demonstrated below:
If now, for example, we delete the Qrt 4 Sales column within the sum range, Excel will automatically adjust the formula, as demonstrated below:
Also read: #VALUE! Error in Excel – How to Fix!
Method #2: #REF! Error Caused By VLOOKUP With Incorrect Cell References
The VLOOKUP function searches for a value in the first column of a table and retrieves a value from a designated column in the corresponding row. However, VLOOKUP returns the #REF! error if it is fed incorrect cell references.
If we attempt to use the formula =VLOOKUP(A9,A2:D6,5,FALSE) to obtain the Qtr 4 Sales of Arizona, we will receive a #REF! error. This is because the Qtr 4 Sales column is not part of the specified range.
How to Fix
To resolve the #REF! error, adjust the range in the formula to include the Qtr 4 Sales column. The updated formula should be =VLOOKUP(A9,A2:E6,5,FALSE) as shown below:
Also read: VLOOKUP Not Working - Fix!
Method #3: #REF! Error Caused By INDEX Function (Incorrect Column/Row Reference)
The INDEX function returns a value or reference of a cell located at the intersection of a specific row and column within a designated range.
However, the INDEX function returns the #REF! error if it is fed an incorrect column or row reference.
In the below example, the formula =INDEX(B2:E6,5,5) returns a #REF! error because the referenced array (B2:E6) is five rows and four columns, but the formula seeks to return a value in the sixth row and sixth column.
How to Fix
- Expand the referenced array to accommodate the row and column references.
- Adjust the row and column references to fall within the referenced array.
Also read: Excel Shortcuts Not Working – How to Fix?
Method #4: #REF! Error Caused By INDIRECT Function Referencing a Closed Workbook
By using the INDIRECT function, it is possible to indirectly refer to a cell or range using a text string.
However, in the case of an external reference to another workbook, it is essential to have the workbook open; otherwise, the INDIRECT function will result in a #REF! Error.
In the following example, the INDIRECT function references a closed “Sales” workbook resulting in the #REF! Error.
Please also note that if the “ref_text” argument refers to a cell range beyond the row limit of 1,048,576 or the column limit of 16,384 (XFD), the INDIRECT function will return a #REF! error.
How to Fix
- Ensure that the referenced workbook is open.
- Ensure that the “ref_text” argument does not refer to a cell range beyond the row limit of 1,048,576 or the column limit of 16,384 (XFD).
Also read: SPILL Error in Excel – How to Fix?
Method #5: #REF! Error Caused By Deletion of a Linked Worksheet
If formulas in your worksheet link to another worksheet within the same workbook or an external workbook, deleting the linked worksheet will result in the formulas returning a #REF! error.
In the example below, the formula in column A has returned a #REF! error because the linked worksheet is deleted.
How to Fix
Regrettably, it is not possible to undo the deletion of a worksheet.
To recover the missing link, you need to recreate the worksheet or restore it from a backup if you have one.
Also read: How to Break Links To External References in Excel?
Method #6: #REF! Error Caused By Deleting a Named Range
If you delete a named range referred to in your formula, you will encounter the #REF! error.
In the example below, the annual sales value in cell B9 has been calculated by summing the named ranges Qtr1_Sales (B7), Qtr2_Sales (C7), Qtr3_Sales (D7), and Qtr4_Sales (E7).
For example, if we accidentally delete the Qtr1_Sales range (B7) and shift cells to the left, we will encounter the #REF! error, as demonstrated below:
How to Fix
If you accidentally deleted a named range, you can restore it by pressing the “Undo” button on the “Quick Access Toolbar” or using the “CTRL+Z” shortcut.
Also read: Excel Table vs. Excel Range – What’s the Difference?
Handling #REF! Error Using IFERROR Function
In situations where #REF! errors cannot be resolved, or you anticipate multiple #REF! errors, you can utilize the IFERROR function to suppress the errors and return a helpful message instead.
The IFERROR function returns the specified “value-if-error” if the evaluated expression results in an error, and returns the original expression’s value if there is no error.
In the following example, the INDIRECT function references a closed “Sales” workbook resulting in the #REF! error.
If you anticipate the #REF! error caused by, for example, the INDIRECT function referencing a closed workbook, as seen in the example above, you can use the formula =IFERROR(INDIRECT(“[Sales.xlsx]”&A2&”!$A$2″),”Data unavailable”).
The formula will suppress the #REF! error and display the informative message “Data unavailable,” as seen below:
IFERROR only allows you to remove the #REF! that you get in a cell and replace it with something more meaningful. It is not a replacement to correct the error caused by addressing the root cause.
Also read: How to Compare Two Columns in Excel (using VLOOKUP & IF)
How to Find All Occurrences of the #REF! Error in a Worksheet
You can use the following techniques to find all occurrences of the #REF! error in a worksheet.
Method #1: Use the Go To Special Dialog Box
You can use the “Go To Special” dialog box to locate all instances of the #REF! error in a worksheet using the below steps:
- On the “Home” tab, open the “Find & Select” drop-down on the “Editing” group and select “Go To Special.”
Alternatively, you can also press the F5 key and then click the “Special” button on the “Go To” dialog box.
- On the “Go To Special” dialog box, select the “Formulas” option, deselect all the checkboxes under it except the “Errors” checkbox, and click “OK.”
All the cells containing the #REF! error are selected.
Note: This method will find all the errors in the worksheets, including the #REF! errors. You can not use this method to only select the cells that have the REF error.
Method #2: Use the Find and Replace Feature to Find All Instances of the #REF! Error in a Worksheet
You can use the “Find and Replace” feature to find all instances of the #REF! error in a worksheet using the following steps:
- On the “Home” tab, open the “Find & Select” drop-down on the “Editing” group and select “Replace.” Alternatively, you can press the shortcut “Ctrl + H.”
- On the “Find and Replace” feature, type “#REF!” (without quotation marks) on the “Find what” field, click “Find All,” and press “Ctrl + A” to select all the cells containing the #REF! error and click “Close” to dismiss the “Find and Replace” feature.
All the cells containing the #REF! Error are selected.
This tutorial showed six examples of how to fix the “#REF!” error in Excel and ensure that your formulas reference valid cells or ranges. We hope you found the tutorial helpful.
Also read: Circular References in Excel – How to Find and Fix it!
Frequently Asked Questions About #REF! Error in Excel
Below are some common queries about handling reference error in Excel.
How to resolve #REF! on a protected sheet?
If you encounter a #REF! error on a protected sheet, you’ll first need to unprotect the sheet by going to the Review tab, clicking Unprotect Sheet, and entering the password if one was set.
Once the sheet is unprotected, you can then identify and correct the cause of the #REF! error. Make sure to re-protect the sheet after resolving the issue.
What leads to #REF! error when opening a file?
A file might display a #REF! error when opening if it contains references to another workbook that is closed or moved, especially when using the INDIRECT function.
To fix this, update the references to the correct workbook or use a direct reference method.
How to correct VLOOKUP #REF! error in another sheet?
To fix a VLOOKUP #REF! error occurring in another sheet, ensure that the formula is referencing the correct sheet and range. Update the formula by double-checking the sheet name, range reference, and column number.
Additionally, make certain that the lookup value and column index are correct in the VLOOKUP function.
Other articles you may also like:
