Have you every seen a #REF! error? It can be frustrating when this error occurs. It’s worse when you’re not sure how to resolve it.
In this article, I’ll show you how to find and fix #REF! errors quickly and efficiently. While seeing an error message in Excel can be stressful, the #REF! error is pretty easy to deal with!
What is a #REF! Error?
Let’s start with the basics. What exactly is a #REF! error?
Excel displays a #REF! error when a formula references a cell that doesn’t exist. At the risk of stating the obvious, #Ref! stands for reference.
A couple of examples might be helpful.
#REF! Error Example #1
In the spreadsheet below, I have used the SUM function to add up a column of numbers. Values in B2:B5 are added up to calculate a total.
If I copy the formula from cell B6 to C2, I get a #REF! error – as displayed in the screenshot below.
Essentially, the formula in cell B6 is adding up numbers the four cells above that position (B2:B5). When I copied the formula to cell C2, a #REF! error is displayed because there aren’t four cells above C2 to add up!
#REF! Error Example #2
In this example, I’m adding a couple of values together with a basic formula in cell I10.
Everything works well… until I delete row 9.
After row 9 is deleted, the formula refers to a cell that is no longer there – and that’s when we see the dreaded #REF! error!
2 Ways to Find #REF! Errors in Excel
In the previous section, we explored what causes #REF! errors. What if you have a large worksheet and you know there are some #REF! errors in it somewhere… you have to find them before you can fix them.
Here are 2 ways to find #REF! errors in Excel!
Method #1: Locate #REF! Errors with Find
One way to locate a #REF! error is to use the Find command. Here is the process.
- Click the Find & Select button in the Home > Editing group and select Find… from the drop-down list that appears.
- Type #REF! in the Find what: box in the Find and Replace dialog box then click Find Next.
- Continue clicking Find Next until you have located #REF! errors.
Method #2: Locate #REF! Errors with Go To Special
The second way to locate #REF! errors involves the Go To Special command. Here’s the process.
- Press the F5 button (or Ctrl + G) to display the Go To dialog box.
- Select Formulas option then uncheck all of the boxes in that category except for Errors.
- Click OK.
How to Fix a #REF! Error
In the previous section, we outlined two ways to locate a #REF! error. Now we’ll look at how to fix a #REF! error.
Since every #REF! error happens because a formula references a cell that doesn’t exist, the only real solution is to add the missing data back in to your worksheet.
I wish the answer was simpler but you may have to put on your detective hat!
In the example below, a #REF! error is displayed because the formula references a delivery charge that was deleted. To resolve this problem, you need to insert a new row, add the delivery charge back in, and update the formula as required.
You need to understand the purpose of your spreadsheet and, ideally, be familiar how the data in your worksheet is structured in order to fix the #REF! error.
