What’s #REF in Excel and How to Fix It
When a formula contains an invalid cell or range reference, the #REF! error is displayed. #REF! in Excel is short for reference, and you’ll usually see it pop up when cells that were referenced in formulas are deleted. They can be a nuisance, but they’re usually pretty simple to find and fix.
Here’s how to get rid of any #REF! errors that come up in your worksheets and a few things you can do to prevent them.
- What a #REF error looks like
- How to quickly find cells with #REF
- Fixing & updating cells with #REF
- Preventing #REF in Excel
What does a #REF! error look like?
If you’re one of the very lucky few who have never seen a #REF! error in their worksheets, this example will show you how it works.
Let’s say you have some values you want to add up. For example, you want to add up the values in the cells B4, B5, B6, B7, and B8. As with just about anything in Excel, there are multiple ways to do this. One way is to use a SUM function that references the entire range B4:B8 like this:
Another way is with a formula that references each cell individually, like this:
In the screenshot below, the highlighted cell B9 used the SUM function and referenced the range, while the individual cell references were used in the selected formula cell D9:
Both formulas provide the correct result of 81.
Now, let’s delete row 4. When we do, the formula in B8 that used a range reference continues to work while the formula in D8 that used individual cell references breaks:
As you can see, the formula that used the range reference was able to adapt to the change, while the formula that specifically referenced a cell that was deleted did not. When you inspect the formula, you’ll notice #REF! in place of the cell that was deleted.
How do you find cells with #REF in Excel quickly?
Here are a few ways to find #REF! errors.
Using Find & Replace to locate #REF errors
- Use the Ctrl+F shortcut to open the Find & Replace dialog box.
- Enter #REF! in the Find field.
- Click the Find All button.
- Optionally: Ctrl+A while dialog is open to select all found cells.
If you want to remove the #REF! references from the formulas, you could select the Replace tab and leave the Replace with field empty. Click Replace All. This replaces all #REF!s with nothing, essentially removing any #REF!s from the worksheet.
Using Go to Special to find #REF in Excel spreadsheets
Another way to find #REF! errors is by using the Go to Special feature.
- You can access it by going to the Home ribbon item and selecting Go To. Alternatively, you can use the Ctrl+G shortcut and select Special in the resulting dialog.
- Then, select Formulas from the resulting Go To Special dialog. Check the box next to Errors.
- The formulas with #REF! errors will be selected.
Using Conditional Formatting to automatically highlight errors
If you wanted Excel to continuously monitor for #REF! errors (and any other formula errors for that matter), you could use conditional formatting. This will essentially apply a designated cell format to any current and future #REF! errors.
- Select all cells (Ctrl+A or click the upper left corner of the worksheet)
- Home > Conditional Formatting > Highlight Cell Rules > More Rules
- Format only cells with: Errors
- Click the Format button to create your desired format
- Click OK
Now, any existing formula error cells are formatted. When you correct them, the designated formatting is removed. And going forward, cells with formula errors will automatically be formatted.
Now that you can find them, how do you fix them?
Finding #REF! errors is the easy part, but how you fix them depends on what you need for your worksheet.
In practice, I will typically review each formula with a #REF! error. I will determine if it is ok to simply delete the #REF! or if I need to point it to a different cell/range. In other words, I need to figure out if the missing reference was important to the current formula. If so, I will correct the formula rather than simply delete the #REF!.
However, if you know that none of the formulas with #REF! need to be updated, and you simply want to remove all #REF!s, a fast way to do that is by using the Find & Replace feature to replace all #REF cells with nothing, as noted above.
Preventing #REF in Excel
The best way to deal with #REF! errors is to never get them in the first place – but that’s easier said than done.
There are a few actions that increase the likelihood of creating #REF! errors in your worksheets. Be careful when performing any of these tasks in Excel.
- Deleting rows and columns – This scenario is a common cause of #REF! errors. Before you get rid of a row or column, make sure your formulas aren’t referencing the values in them.
- Copy + pasting cells – If you’re copying and pasting a cell that uses relative references, you may get a #REF! error because Excel updates them based on where they’re pasted in the worksheet. For example, if you copy a cell with a formula that references the cell to the left, and the paste that into a cell in Column A, there are no valid references to the left. This results in a #REF! error. Note that in some cases, this could be avoided by changing such relative references to absolute.
Do you have any other tips for preventing or fixing #REF! errors in your worksheets? Let us know in the comments!
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.