If you have worked with formulas in Excel for some time, I am sure you have already met the #REF! Error (reference error).
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.
Related tutorial: Delete Rows Based on a Cell Value (or Condition) in Excel
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’:
- Select the entire dataset where you want to check
- Hold the Control key and press the F key (or Command + F if you’re using a Mac). This will open the Find and Replace dialog box
- In the ‘Find what’ field, enter #REF!
- Click on the ‘Find All’ button. This will find all the cells that have the #REF! error
- Hold the Control key and press the A key (or Command + A if you’re using Mac). This would select all the cells that have the reference error.
- Close the Find and Replace dialog box.
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:
- Select the entire dataset where you want to check for errors
- Click the Home tab
- In the Editing group, click on the ‘Find & Select’ option.
- In the options that show up, click on the ‘Go-To Special’ option
- In the Go To Special dialog box, click on the Formulas option
- In the options within Formulas, uncheck everything except the Error checkbox.
- Click OK
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: