Md. Sourov Hossain Mithun
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 that 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.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
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 for the following factors:
- If you delete Row, column, or sheet.
- If you give incorrect or invalid cell references in a formula.
6 Solutions: Fix #REF! Error in Excel
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 then see 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.
Read More: Excel Error: The Number in This Cell is Formatted as Text (7 Fixes)
2. Fix Tab Reference to Remove #REF! Error in Excel
Have a look 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: REF Error in Excel (9 Suitable Examples)
3. Fix Cutting and Pasting Issues to Solve #REF! Error in Excel
I have added another years’ sales to my dataset. Now if I cut it and paste it to Column C then see at 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-
Related Content: Reasons and Corrections of NAME Error in Excel (10 Examples)
Similar Readings
- How to Fix “Fixed Objects Will Move” in Excel (4 Solutions)
- [Fixed!] ‘There Isn’t Enough Memory’ Error in Excel (8 Reasons)
- How to Correct a Spill (#SPILL!) Error in Excel (7 Easy Fixes)
- [Fixed!] Error 509 in Excel (4 Possible Solutions)
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 upper than the source cell then the formula changes its references along 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.
Read More: Errors in Excel and Their Meaning (15 Different Errors)
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 for finding 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.
Related Content: [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 workbooks 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 and keep the Replace with box empty.
- Finally, press Replace All.
Then Excel will remove all the errors.
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.
