Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles Blog The #REF Excel Error – How to Find and Fix #REF Errors in Excel

The #REF Excel Error – How to Find and Fix #REF Errors in Excel

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. 

Woman questioning a problem on her laptop computer.

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. 

Excel sheet listing numbers with a sum.

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. 

Excel sheet showing 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. 

Go to special formulas box.

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. 

Find and replace box in excel.

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. 

Vlookup function in excel.

When the column figure is correctly set to 2, the #REF error disappears, and the correct data is displayed. 

Lookup function matching an item in excel.

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

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc