Excel triggers the #REF! error when it cannot locate the argument you referenced in your formula. This error generally occurs when your references are moved or deleted.
Apart from switching your data’s location, You will encounter the #REF! error due to a number of other reasons. Other reasons include closing the external spreadsheets or programs you used to reference data in your current spreadsheet.
What is #REF! Error?
Excel cannot carry out your calculation if it cannot locate the data you requested in the specified location. For example, if I used the range A2:A11 as an argument for my SUM function and then, delete the entire range, SUM will return the #REF! error.
You will also commonly encounter the #REF! error in PivotTables. This is because PivotTables do not store their data in cells. So, when you hide or add new fields to your PivotTable, the references get changed, and Excel triggers the #REF! error.
Some of the common reasons for the #REF! error in Excel are:
- Deleted Reference
- Reference Moved
- The source Worksheet is Deleted
- The source Program is Closed
- The referenced cell contains the #REF error
- Incorrect DDE Reference
- Error with Macro
How to Fix #REF! Error in Excel
Recover Deleted Data
If you suspect experiencing the #REF! error because you deleted the source data, your first attempt should be to recover it. If this was your last action, use Ctrl + Z to revert your last action. Unfortunately, if you’re running into the #REF! error after deleting the source worksheet, there is nothing you can do as this action cannot be reverted.
If you had saved your worksheet prior to deleting the data, you can close your worksheet without saving the changes. However, remember this will also remove other changes you made to the spreadsheet.
Update Formula
If you’ve run into the #REF! error as a result of moving data across sheets, you can update your formula to reference the cells that now hold your data.
- Select the cell with the #REF! error.
- On your keyboard, select F2.
- Go to the formula bar and change the references.
Re-open Closed Workbook
If you’ve inserted dynamic arrays in your spreadsheet using functions like the INDIRECT function, closing the source worksheet will trigger the #REF! Error.
You can re-open the closed workbook from recently opened files in Excel. Go to File and locate your document under Recent. Once you’ve located it, click on it once to open it.
Convert Formula Results to Static Values
I have seen this one far too many times to not mention it in this article. Most of the time, when we convert data using functions like UPPER for texts and VALUE for numbers, we tend to paste the resulting values in the location of the source range.
Unfortunately, by removing the source data, you’re removing the reference for the formula that is displaying the results. If you’ve come across a similar issue, paste your formula results as static values in the source range.
- Select your result range.
- Head to the source range and right-click.
- Press V on your keyboard.
Trace Error
Funnily, this error reminds me of the “one rotten apple spoils the whole barrel” saying. If your active cell is referencing a cell with the #REF! error, your active cell will also display the #REF error.
It can be a bit challenging to identify such errors in a large spreadsheet. However, Excel has the Trace Error tool which will easily mark which cell is causing the #REF! issue.
- Select the cell with the #REF! error.
- Go to the Formulas tab.
- From the Formula Auditing section, select Error Checking.
- Click Trace Error.
Solve #REF! in PivotTables
If you’ve used lookup functions like VLOOKUP on your PivotTables, you might encounter the #REF! error when you’ve disabled your data field.
You can solve this error by bringing the disabled data field back in view. In this PivotTable, I ran into the #REF! error after I disabled the filed “quantity”. Here’s how I bring the field back into display:
- Select the PivotTable.
- If you don’t see a sidebar, go to the PivotTable Analyze > Field List.
- Under PivotTable fields, check quantity.
VLOOKUP might give an incorrect result depending on the new location of your data. Therefore, I suggest you look into the GETPIVOTDATA function instead of lookup values in a PivotTable.
Re-Open the Embedded Program
Excel will trigger the #REF! error if you close the source program for an OLE (Object Linking and Embedding) object.
Microsoft 365 has this feature to make data transfer convenient among its programs. However, these elements are dynamic and you must keep the source program open at all times. If the program you’ve linked is closed, Excel cannot locate the source of the value you wished to embed.
You need to reopen the embedded program to solve the #REF! error. When you reopen the program, the exchange of information between the programs is re-initiated, solving the #REF! error.
Unblock External Connections
Microsoft allows users to connect Office programs to other Windows-based programs through DDE (Dynamic Data Exchange). You will need to reference a topic you want Excel to connect to. If you incorrectly enter this topic, you will receive the #REF! error.
However, if you fix your references and are still experiencing this error, then you may have to enable unblock external content from your Trust Centre Settings.
- Go to File > Options.
- Head to Trust Center > Trust Centre settings.
- Select External Content.
- Under Security Settings for Data Connection, choose either:
- Enable all Data Connections (not recommended): Accepts all data connections without notifying you.
- Prompt user about Data Connections: Excel will ask for your permission before allowing any data connection.
- Click OK > OK.
Update Macro
If your macro is referencing a cell above the cells in row 1:1, it will result in the #REF! error. This is because there is no row before 1:1. When Excel can’t find the specified cell or range, it displays this error.
You can check your macro references from the VBA editor.
- Head to the View tab from the menu bar.
- Select the Macros option.
- Choose your macro from the Macro window > Edit.
- Make the necessary updates.
Use IFERROR
Sometimes, we just know that we’re bound to receive the #REF! error. This is when we’re creating templates with only formulas, or when we’re yet to enter data on our formula sheet. In these cases, the #REF! error isn’t alerting us of a problem we don’t know. So, you might just want to get rid of it.
In such cases, the IFERROR function comes really handy. You can nest your formula in the IFERROR function to avoid any Excel errors.
Here is how you can enter the IFERROR function when creating a formula:
=IFERROR(value, value_if_error)Let’s use IFERROR in a formula that is currently returning me the #REF! error. Here, cell F4 is returning the #REF! error as cell D3 has a #REF! error. Let’s nest it inside the IFERROR function and see what it results in.
=IFERROR(SUM(A2:A21,D3),0)
Instead of #REF!, it returned 0.