It can be frustrating when your Excel spreadsheet doesn’t act the way you expect. This can often happen when you are working with formulas, where you might get a series of errors. One of the most common errors in Excel related to formulas is the #REF! error. Here is why this might occur and how you can fix it.
Learn how to recognize and resolve #REF! errors in Excel with this guide.
About the #REF Error in Excel
The #REF error in Excel stands for “reference,” meaning that a cell that is referenced in a formula no longer exists or is valid for the operation. These errors often occur because data is moved or rows, columns, or sheets have been moved. It can also happen when you copy a formula that has relative references to a new location, and those references are no longer valid.
A simple example of a #REF error is below. The first image shows a formula with four numbers added together (15,31,3, and 55). In Cell C8, we show the formula adding these figures together.
The next figure shows what happens if row 5 is deleted. Now that one of the references in the formulas is no longer valid, it returns a #REF error.
Preventing #REF Errors
The best way to deal with #REF errors is never to get them or to prevent them from occurring. How can you do this? First, check the formulas in your worksheet before you delete rows, columns, or sheets. If a formula will be affected, think twice.
Next, if you are copying and pasting formulas between locations, check for relative references. If these exist, you might want to convert them to an absolute reference to prevent any changes during the copy/paste operation.
How to Find #REF Excel Errors
A #REF error might be obvious when you see that one of your formulas isn’t working. On the other hand, you might have a large spreadsheet and be unsure where your various issues lie. There are two different ways to located #REF errors in an Excel sheet.
1. The first way is to press either F5 or Control-G (Go To) and then select Special. After you do this, the Go To Special box will appear. Then, select Formulas, and only check the box that says “Errors.” Click OK. Excel will then take you to every cell that contains an error.
2. The other way to find #REF errors in Excel is by using the “Find” function. Press Ctrl-F or located “Find” under “Find & Select.” Select the tab labeled “Find.” Enter #REF in the Find what section, and hit either “Find All” or “Find Next.” You can then go through all of the errors and make some decisions.
Fixing a #REF Error in Excel
Fixing a #REF error can be tricky because the original cell that is supposed to be referenced is often deleted. If you know what that reference is supposed to be, you can just fix the error manually. If you are sure what the reference refers to, you might have to do some more research or backtracking so that you can fix your formula.
You can also use the Find and Replace tool in Excel to clear out all of the #REF errors in your Excel sheet. Open up the tool (same as above) and select the Replace tab. In Find what: put #REF and leave Replace with: blank. Select “Replace All.” While this will clear out all of the errors and make your formulas work again, this does not mean that they will work as intended since you’ve deleted some data. It’s best to double-check these after running this operation.
Because #REF! errors can be compounding, the best course of action is to fix them as quickly as possible. In other worse, assuming you delete a column or row and then see that you have several #REF errors, undo that last action. The errors should disappear, and you fix your formulas first to either exclude the cells you want to delete or not return an error.
One particular thing to note is that you can’t undo the deletion of a sheet in Excel. If you delete a tab in your worksheet and then have #REF errors, you can’t undo this. The quickest way to backtrack and fix this error is to close the Excel file without saving it and open the last saved version, which was hopefully before you deleted that sheet.
What About the VLOOKUP #REF Error?
You can also get a #REF error with the VLOOKUP function if a column isn’t correctly specified. In the image below, the VLOOKUP function returns a #REF! error because the table range does not have a column 3.
When the column figure is correctly set to 2, the #REF error disappears, and the correct data is displayed.
Other Possible #REF Error Situations
There are just a few more common instances in which #REF errors in Excel occur:
- Macro Issues. If a macro refers to a cell above a function, but the cell above that function is in row 1, you’ll get a #REF error. This might require editing of the macro.
- DDE Issues. If you’re using Dynamic Data Exchange (DDE) and getting a #REF error on a topic, first check that you are referencing the right topic and then review your external content settings in the Trust Center Settings.
- OLE Issues. If an Object Linking and Embedding (OLE) link returns a #REF error, check the program that the link references.
Still Need Help With Excel Troubleshooting?
This is most of what you need to know about the #REF error, but we might not have covered everything. Maybe you’re having trouble finding or fixing an error, or just don’t want to jump through all these hoops. That’s ok. Excelchat can provide problem-specific help 24/7.
Get the one-on-one help you need with Excel now. Your first session is always free.
Leave a Comment