In this tutorial, I will explain the details of reference (#REF!) errors and guide you on how to find the errors in excel. Besides, I will mention possible reasons for this error. For your better understanding, I will briefly discuss how you can prevent and fix the reference (#REF!) errors.
What is Reference (#REF!) Error in Excel?
Usually, the reference (#REF!) error shows up when a cell referred to the Excel formula is not valid. This might happen when we move or delete Excel columns/rows/sheets which are referred to as a formula. Moreover, this error may happen if you copy a formula containing a relative reference to a new location where references are invalid. Let’s consider an example of #REF! error. Suppose, I have a dataset containing several items’ sales data and I calculated total sales using a formula. Here the summation formula used cell C5 and cell D5 as reference.
Now, if I delete column C, immediately #REF! errors appear as the reference column used in the above formula no longer exists.
How to Find Reference (#REF) Errors in Excel: 3 Easy Methods
While working with a large number of data, reference errors might be present anywhere in the worksheet. Searching for #REF errors one by one might seem a time-consuming task. I will show you some methods to find reference (#REF) errors at once, using Excel features and VBA.
1. Use ‘Go To Special’ Option to Find Reference (#REF!) Errors
You can use the ‘Go To Special’ option to find reference errors in Excel.
Steps:
- Select the entire worksheet that contains reference (#REF!) errors.
- Next press Ctrl + G to bring the ‘Go To’ dialog box. Then click on Special.
- As a result, the ‘Go To Special’ dialog box will appear. Now, select the Formulas option and put a checkmark only on Errors. Press OK.
- Consequently, excel will bring you to all the cells that have #REF! errors.
⏩ Note:
- You can bring the Go To Special dialog by following the path: Home > Editing group > Find & Select > Go To Special.
Read More: How to Remove Error in Excel
2. Excel Find Option to Search Reference (#REF!) Errors
This time I will use the Find option in Excel to find #REF! errors with the same dataset.
Steps:
- First, select the entire worksheet that has the errors. Then press Ctrl + F to get the Find and Replace dialog box. Now type #REF! in the Find what box and click Find All.
- Upon clicking Find All, you will get a list indicating which cell has the #REF! errors. After you are done with the output, click Close to finish the operation.
3. Excel VBA to Find Reference (#REF!) Errors
You can identify which cells have #REF! errors using a simple VBA code. Here, we used the same dataset as above.
Steps:
- First, go to the worksheet where you want to locate the reference error, right-click on the sheet name, and press View Code to bring the VBA window.
- Now type the below code in the Module and run the code by pressing F5 from the keyboard.
Sub Get_REF()
Dim a As Range
For Each a In ActiveSheet.UsedRange
If a.HasFormula = True And InStr(1, a.Formula, "#REF!", 1) > 1 Then MsgBox a.Address
Next a
End Sub
- Once you run the code, a message box will display the cell reference that has the #REF! errors. Keep pressing OK until you get the last cell reference.
VLOOKUP Reference (#REF!) Errors in Excel
You can get reference errors while applying the VLOOKUP function in Excel. Such as, while entering the arguments in the formula, if you do not specify the column index properly, #REF! errors will happen.
For example, the below formula returns #REF! because the column index number is entered as 4. The reason is here the lookup table has only 3 columns. So, obviously, the formula will return an error.
However, if you insert 2, instead of 4, the error is fixed.
Other Possible Reasons for Excel Reference (#REF!) Errors
- Macro Issues: Sometimes, macro refers to a cell above a function where the cell above that function is in row 1. In that case, you might get #REF errors. In a situation like this, we might need to edit the macro.
- Object Linking and Embedding (OLE) Issues: When an OLE link returns #REF errors, run the program that the link is referencing.
- Dynamic Data Exchange (DDE) Issues: While using DDE make sure you are referencing the right topic. If an error occurs, from Excel Trust Center Settings review the external content settings.
Prevent Reference (#REF!) Errors in Excel
The best way to prevent #REF! error is to prevent its occurrence. Still, consider the below points:
- Modify your formula so that the deletion of columns/ row/ sheets won’t affect them. For instance, you can use a range of formulas for calculating summation ‘=SUM(C5:D5)’ instead of using a simple summation formula (=C5+D5).
- If deleting cells/columns/sheets that affect your formula, think twice before deleting them.
- Sometimes, you may need to copy formulas to other locations. In such cases try to avoid using relative references, instead, use absolute references in formulas.
Fix Reference (#REF!) Errors in Excel
Fixing #REF! errors are pretty tricky. I can mention some basic suggestions below:
- If you just deleted a cell/row/column and #REF! errors occur as a result of that, just undo the deletion.
- If you have deleted a worksheet, and reference errors appear due to that, just close your Excel file without saving it. Then reopen the Excel file. We are doing this because, if you delete an Excel sheet, there is no option to undo it.
- You can replace the #REF! errors with any other text or value. While doing that, you can use the Find and Replace option in Excel.
- Try to fix the errors manually immediately after the error occurs. Sometimes you might need to do back-calculation while fixing the formula.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
Conclusion
In the above article, I have tried to discuss several suggestions for finding reference errors in Excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.
