In this tutorial, we will know what is #REF Error in Excel at the same time why and when do we get this error in our calculations.
Facing errors in Excel is the last resort we will want to see working with Excel. It’s quite frustrating when we see it appear everywhere, especially we don’t know where it comes from and what causes it.
However, fixing this error is overwhelming and determines our expertise in Excel.
Here’s how to get rid of any #REF! errors that come up in your worksheets and a few things you can do to prevent them.
What is #REF Error in Excel
#REF! in Excel is basically short for reference. Usually, this occurs when cells that are referenced in formulas are deleted. This particularly bothering, however, it’s simple to fix and find.
Particularly, this error occurs when the reference used is invalid. Usually, the invalid reference is the result of accidentally created when copying or moving formulas from one cell to another.
How to find cells with #REF in Excel quickly?
After we know what an error is. Now we will know how to find it.
So here are some ways to find #REF! errors.
Using Find & Replace to locate #REF errors
- To find Find and Replace dialog box, just Press Ctrl+F to open.
- In the Find field, enter the #REF! error.
- Then click the Find All Button.
- To select all found cells Press CTRL+A while dialog is open.
When you want to remove the #REF! from the formula, just select Replace tab and leave the Replace with field empty. Then Click Replace All.
This will replace all the #REF! with empty cells and essentially remove any #REF!s from the worksheet.
How to fix #REF Error in Excel
Finding #REF! errors are the easy part, but how to fix it will rely on what you need in your worksheet.
Practically the best thing to review all the formulas contains #REF error. This will determine if the best thing to respond it’s either to delete or put it in another cell or range.
Simply it requires analysis if the data is critical to the current formula or not. If so, then correct the formula rather than delete it.
Nonetheless, if the #REF! in a formula does not need to be updated simply remove it all. Additionally, use Find And Replace feature for a fast way of removing all #REF! error.
The tricky thing is, that this formula is no longer visible. That is why it is highly recommended to resolve #REF errors when they arise.
If not, the only way out is to carefully examine the sheet and figure out what should have been the correct formula. In case, a REF error is caused by a deleted sheet of row or column, it is recommended that you restore the previous version of the sheet.
#REF Error In Excel VLOOKUP
You may also encounter #REF error in working with VLOOKUP in Excel.
You will see this error in VLOOKUP when a column specified is incorrect. In the screen below, VLOOKUP returns #REF! because there is no column 3 in the table range, which is A2:B10.
When the column index is set to the correct value of 1 and remove another range of cells the #REF! error is resolved and VLOOKUP works properly:
This error also can be encountered in INDEX Function when a row or column is invalid.
Preventing #REF in Excel
Being unaware of performing tasks in Excel can increase the chance to get #REF! errors in worksheets. Therefore, familiar with the following actions below:
- Deleting rows and columns – This case is a common reason why we get #REF! error. Keep in mind before deleting rows and columns to make sure the formulas aren’t referencing them.
- Copy + pasting cells –When copying and pasting a formula that uses relative reference it may result in #REF! error since the cells updates depending on where you pasted in your worksheet.
Conclusion
That’s all we need to know about #REF! error in Excel. So in this tutorial, we covered the possible reason along with possible ways to get rid of this error in our worksheet.
Now that you know what cause of this error it is easy for you now to troubleshoot your calculations when you faced this kind of error.
I hope this tutorial is useful in doing your task. Thank you for reading 🙂