While working in Excel, facing the #REF! error is one of the most common errors. But no worries, we are here to rescue you from those errors. This article will explain to you the reasons for the error and will provide you with 6 quick ways to fix the #REF! error in Excel.
What Is #REF! Error in Excel?
The #REF! error stands for the wrong reference. You will get this error when the cell reference is no longer valid or does not exist.
It can happen because of the following factors:
- If you delete a Row, column, or sheet.
- If you give incorrect or invalid cell references in a formula.
Read More: Errors in Excel and Their Meaning
How to Fix #REF! Error in Excel: 6 Effective Solutions
Let’s get introduced to our dataset first. It represents some salespersons’ sales in different years.
1. Fix #REF! Error with Correct Cell Reference in Excel
Here I have used a formula to sum up the total sales for every salesperson.
But when I deleted Column C and then saw what happened, created the #REF! error. Because the references from the column are missing, that’s why it is showing the #REF! error.
Solution:
- Press Ctrl+Z to undo your operation or write the formula again with corrections.
2. Fix Tab Reference to Remove #REF! Error in Excel
Have a look at that, I have used another sheet reference to calculate the total sales. The sheet is named ‘2019’.
Let’s delete the sheet and see what happens.
It created #REF! error because that sheet is missing.
Solution:
- You can’t recover a sheet after deletion. So close the Excel file if you haven’t saved it yet and reopen it.
- Or erase the reference which you used from the deleted sheet.
Read More: How to Find Reference Errors in Excel
3. Fix Cutting and Pasting Issues to Solve #REF! Error in Excel
I have added another year’s sales to my dataset. Now if I cut it and paste it into Column C then see in the next image what happened.
It’s showing #REF! error.
Solution:
- Undo the operation by pressing Ctrl+Z.
- Or copy instead of cutting to fix the #REF! error in Excel.
Output after copying-
4. Fix Relative Reference to Solve #REF! Error in Excel
In Cell E6 I have added an extra amount by using the cell reference G1. It’s working, no problem here.
But when I copied Cell E6 to G5 then #REF! Error occurred. It happens because if you copy a formula to a cell higher than the source cell then the formula changes its references upward too. But there is no cell above G1 that’s why the error occurred.
Solution:
- Use absolute reference for that kind of cell and then copy the formula to fix the #REF! error in Excel.
Now it worked, the error is gone.
5. Fix VLOOKUP Invalid Reference to Erase #REF! Error in Excel
Here, we’ll explain the #REF! error with the VLOOKUP function. I have used the VLOOKUP function to find the sales for Aron but it is showing #REF! error.
The reason is I have placed the wrong column index number- 5. But my array has only 4 columns.
Solution:
- Give the correct column index number- 2,3, or 4. I put 4.
Read More: [Fixed!] Excel Found a Problem with One or More Formula References in This Worksheet
6. Fix INDIRECT Invalid Reference to Erase #REF! Error in Excel
Here I have used the INDIRECT function and cell references from another workbook sheet named ‘Sales’.
Now let’s close that workbook.
Then do another operation, like I have calculated the sum total for every year. Soon after you will see that the previous formula caused #REF! error.
Solution:
- Reopen that workbook to fix the #REF! error in Excel.
How to Clear Multiple #REF! Errors
In column E there is multiple #REF! Errors. If you want to remove them at a time then use the Find and Replace tool.
Steps:
- Select the column and press Ctrl+H and the Find and Replace dialog box will appear.
- Then type #REF! In the Find what box keep the Replace with box empty.
- Finally, press Replace All.
Then Excel will remove all the errors.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
Conclusion
I hope the procedures described above will be good enough to fix the #REF! error in Excel. Feel free to ask any question in the comment section and please give me feedback.
