Since this is quite common, I thought of writing a tutorial just to tackle the #REF! error. In this Excel tutorial, I will cover what is the #REF! error, what causes it, and how to fix it.
What is #REF! Error in Excel?
A reference error is something you get when your formula doesn’t know what cell/range to refer to or when the reference is not valid. The most common reason you may end up with the reference error is when you have a formula that refers to a cell/row/column and you delete that cell/row/column. Now, since the formula (that was referring to the range before it got deleted) has no clue where to point to, that earlier reference in the formula changes to #REF!. This, in turn. makes the formula return the #REF! error for the formula result.
Example of the #REF! Error
Let me show you a simple example where we end up getting the reference error and later I will cover how to find and fix it. Below is a dataset where I want to add the four cells, and I use simple arithmetic using the addition operator.
So far so good! Now, if I delete one of the columns, the formula will not have the reference for the deleted cell, so it’s going to give me a #REF! error (as shown below)
In the above example, since I deleted the fourth row (that had the cell with the value 3), the formula doesn’t know what to refer to and returns a reference error. Reference errors are quite common and you may want to do a quick check before you use a dataset in the calculation (that has the possibility of having the #REF! error). So, let me show you two ways to quickly find cells that have the reference error and some possible fixes.
Find #REF! Error using Find and Replace
Suppose you have a dataset as shown below where you have a couple of reference errors.
Below are the steps to find and select all the cells that have the ‘reference errors’: The above steps would find and then select all the cells that have the #REF! error and select these. Once you have all these cells selected that has the error, you can choose what to do with it. Let’s have a look at some of the possible ways you can handle the reference error.
Ways to Fix the #REF! error
Once you have all the cells with the reference error selected, you can choose to do any of the following:
Delete the error: Simply hit the delete key and it will remove these error and you’ll have blank cells instead Replace with a value or text: You can choose to replace the error with 0 or dash or any other meaningful text. To do this, simply type what you want to replace the error with, hold the Control key and then press the Enter key. This will enter the text you entered in all the selected cells. Highlight these cells using the cell color option in the Home tab in the ribbon
Note: When you use the Find and Replace method, it will only find the cells that have the #REF! error, as that’s what we searched for in the ‘Find what’ field. In case there are other errors (such as #NA or #DIV! error), these will not be found (unless you repeat the same steps for these errors as well. In case you want to find all the errors (including the #REF! error), use the method covered next.
Find Errors using Go To Special Option
Another method to quickly find #REF! errors which are a result of a formula is by using the Paste Special method. The good part about this method is that it will find and select all the cells that have any type of error (including the reference errors). But the downside with this method is that it will only find and select cells where the error is because of formula. In case the error is there as text, this method will not be able to find it. Suppose you have a dataset as shown below and you want to find and select all the cells that have errors in it.
Below are the steps to use Go To Special option to find and select all the errors: The above steps would instantly select all the cells that have formulas that return an error. Once you have these cells selected, you can choose to delete these, highlight it, or replace it with 0 or dash or some other meaningful text. So these are two quick methods you can use to find and fix the #REF error (reference error) in Excel. Hope you found this tutorial useful. You may also like the following Excel tutorials:
Use IFERROR with VLOOKUP to Get Rid of #N/A Errors How to Quickly Find Hyperlinks in Excel How to Find Hyperlinks in Excel Identify Errors Using Excel Formula Debugging Excel IFERROR Function #NAME Error in Excel – What Causes it and How to Fix it! How to Get Rid of #DIV/0! Error in Excel? How to Fix #VALUE Error in Excel?
