How to Fix the #REF Error in Excel? 3 Easy Methods
(Note: This guide on how to fix the #REF Error in Excel is suitable for all Excel versions including Office 365)
Facing error messages is the last thing you want to see in Excel. They are quite frustrating to deal with and often difficult to resolve. The Excel #REF! Error is one of the most dreaded error messages in Excel. If you don’t know what you are doing, it may take forever to resolve this error.
But, don’t worry. In this guide, I will give you some practical tips to avoid this error and resolve it easily.
You’ll learn:
- What Causes the REF Error in Excel?
- Method 1: How to Prevent the #REF Error in Excel?
- Method 2: How to Find Multiple #REF! Errors in Excel?
- Method 3: How to Fix the #REF Error in Excel?
Related:
How to Fix the Excel Circular Reference Error?
How to Fix the Excel NAME Error? 3 Simple Tips
How to Fix the Excel Spill Error? — 5 Easy Fixes
What Causes the REF Error in Excel?
Using invalid references inside Excel formulas is the main cause behind the Excel REF error. But, keep in mind that almost all of these invalid references are accidentally created when copying or moving formulas from one cell to another.
Let me explain this with an example.
Let us say you want to calculate the total sales achieved in the last quarter. You calculate this in cell B5 using the formula SUM(B2:B4), but later decide to move it to cell E2. Excel will throw up a #REF error, leaving you confused.
What is even more perplexing is that, if you try to move the formula to cell E9, Excel will return a 0 instead of the #REF error.
So, what is causing all this?
The real culprit behind the Excel REF error is the inappropriate usage of relative references.
You see, when you use the relative reference B2:B4 in the SUM function in cell B5, you are asking Excel to find the sum of the previous three cell values i.e B2, B3 and B4.
When you try to move the formula to cell E2, there are no three cells preceding it. That’s why this is considered an invalid reference and leads to the #REF error. On the other hand, if you try to move the formula to cell E9, there are in fact three cells preceding it albeit empty (E6, E7 and E8). This is the reason, Excel returns a 0 instead of the #REF error.
Method 1: How to Prevent the #REF Error in Excel?
The best way to prevent #REF errors from cropping up is to use absolute references as much as possible. Also, keep in mind that, accidentally deleting rows or columns which are already used in calculations may also lead to reference errors.
Pro Tip: It is always better to resolve #REF errors as soon as they appear (By using the Undo button and using the correct reference). Doing this will save you from a lot of pain and confusion. Otherwise, you will end up spending a lot of time figuring out what caused the REF error.
Method 2: How to Find Multiple #REF! Errors in Excel?
In case your worksheet already has a lot of reference errors, fixing them is going to be quite a challenge, to be honest. But, on the other hand, finding them is very easy.
To find REF errors scattered over an Excel worksheet, just follow these steps:
- Press Ctrl+H on your keyboard and open the Find and Replace dialog box.
- Type #REF! and click on the Find All option. This will highlight all instances of reference errors.
- Click on Find Next to cycle through the list of REF errors in the sheet and fix them one by one.
Also Read:
How to Use Goal Seek in Excel? (3 Simple Examples)
How to Insert Multiple Rows in Excel? The 4 Best Methods
How to Autofit Excel Cells? 3 Best Methods
Method 3: How to Fix the #REF Error in Excel?
The only way to fix a #REF error is to figure out what was wrong with the formula reference before and replace it with the correct reference. The tricky thing is, that this formula is no longer visible. That is why it is highly recommended to resolve #REF errors as and 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 or row or column, it is recommended that you restore the previous version of the sheet.
Note: If your #REF error is caused by a VLOOKUP formula, it is most probably due to an incorrect column reference. For example, let us say use the formula =VLOOKUP(B20, C15:D17, 3) but, there is no column 3 in the range C15:D17. This will cause a #REF error. To fix it, just change the index to a proper value.
Suggested Reads:
How to Group Worksheets in Excel? (In 3 Simple Steps)
How to Shade Every Other Row in Excel? (5 Best Methods)
How to Use the Excel Fill Handle Easily? (Top 3 Uses with Examples)
Closing Thoughts
In this guide, we saw how to smartly avoid and fix #REF errors in Excel. I hope you find this guide helpful. If you have any questions, please feel free to ask us in the comments. We are always happy to help.
If you need more high-quality Excel guides, please check out our free Excel resources center.
Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
