What Does #REF Mean in Excel And How to Quickly Resolve It!
Jan 05, 2023
Getting the #REF error message in Excel is incredibly frustrating. Sometimes it can completely knock you off your train of thought and leave you scratching your head, trying to understand why it has occurred.
Often the error occurs even when you think you have used valid references, which makes it almost impossible to establish the root cause of the issue.
In this article, we will look at what a #REF error in Excel means and how to locate, resolve, and avoid them in the future.
Suggested read: AI for Excel
What is a #REF Error in Excel?
First, let's focus on a #REF error in Excel. A #REF error will show when using a formula referring to an invalid cell. In most instances, the cell has either gotten pasted over or deleted, which has resulted in it becoming invalid.
As a result, this action can lead to cells that once showed important data now only showing the #REF error message. Essentially, this message tells you that the formula used to calculate the answer in this cell no longer has relative references. Therefore the data can no longer be shown.
How do You Locate an Invalid Cell Reference?
Fortunately, locating invalid cell references is much easier than you might think, and this process can quickly help you establish the root cause of the issue and amend it.
For example, if you had the formula: "=A1+A5+A9" showing in cell F7, a #REF error could occur if you delete the fifth row of your worksheet.
You must take the following steps to find the #REF cells within your worksheet.
-
Click on the Excel Ribbon, choose Home, then Find and Select, then Go To Special.
-
The Go To Special dialog box will appear from here, and you can choose Formulas. Ensure you untick every box apart from Errors before hitting OK.
If you have followed those steps precisely, the only selected cells are those with the #REF error message.
While this is the most common way to cause a #REF error in Excel, you can also do so by having the wrong row or column reference in an INDEX function., You might also see a #REF error if you've inputted incorrect range references within a VLOOKUP function. The final but least common ways include Object Linking and Embedding issues, Dynamic Data Exchange, or Macro mistakes.
Suggested read: How to Remove a VLOOKUP Formula in Excel
How to Fix #REF! Errors in Excel
Now that you have identified your #REF errors, it's time to eliminate them and get your worksheet back on track!
The best way to solve the issue of #REF errors is by utilizing the Find and Replace feature. This feature makes it quick and easy to replace #REF errors with blanks, which stops them from messing up your data sheet. Here's how to do it:
-
From the Excel Ribbon, click the Home button, then Find & Select, then Replace. You could also open the Find and Replace dialog box by hitting CTRL + F.
-
From there, the Find and Replace window will appear, and within the Find box, type #REF while leaving the Replace box empty.
-
After that, press Replace All and hit the Close button.
-
A pop-up button will appear, which tells you all the cells where replacements were made. Hit OK, and the reference errors should've gotten resolved.
How to Fix #REF! Errors in Google Sheets
Fortunately, solving the REF error issue in Google Sheets is similar. If a deleted cell is causing the issue, you can fix it using the same Find and Replace feature.
If you want to learn more about other essential Excel and Google tricks, such as using the SUM function, executing a column lookup value, or using a VLOOKUP formula, check out our other articles on the Simple Sheets blog!
Related Articles to #REF Excel Errors:
Microsoft Access vs. Excel: Which One Should I Use and Why?
Excel Cannot Group Dates in Pivot Table: 3 Quick Fixes to Try.
Want to Make Excel Work for You? Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.
