Most of the time, #REF! Error occurs when you have entered a formula in a cell, and the reference in that formula is invalid. So, for example, let’s say you have a formula that sums values from five cells above, and copy and paste that formula to a location where you only have three cells above; you will get the #REF! Error.
Copied Formula to a New Location
The example below shows the sum formula in cell A5, which sums values from the range A1:A5. You have relative cell reference in the formula.
And when you copy and paste this formula to cell C3, it returns #REF! in the result.
As I said earlier, when you copy the formula from A6 to C3, C3 doesn’t have five cells above, which means it’s impossible to refer to five cells from above.
#REF! Error with VLOOKUP
While using VLOOKUP, specify the column from which you want to get the value.
In the above example, we have specified three as col_index_num in the formula. But, in the lookup table, we have only two columns, and that’s why we have the #REF! Error in the result.
You might face the same thing while using INDEX. When you try to look for a row or a column number that is higher than the total number of columns or rows you have in the referred data, this returns a #REF! Error in the result.
In the above example, we are looking for the value in the 7th row in the range, but we have only 6 cells in total.
#REF! Error When You Delete a Column
In the example below, we have referred to the range A1:C3 in three parts.
But if you delete any of these columns, the formula will return a #REF! Error in the result.
Other Reasons
While using the INDIRECT function, if you are referring to a cell or range from a closed workbook. There’s a chance that you will get the #REF! Error in the result.
Fixing the #REF! Error
In Excel, when you are working formulas, it’s better not to trap the #REF! Error with the ISERROR or any other function. It’s better to fix the error when you get it in the result.
In most cases, you need to fix the reference to the range or cell, and your formulas will work fine. But it can be tricky sometimes to know exactly which reference is deleted or removed.
For example, when you delete a sheet from a workbook, it’s impossible to undo that action, and it’s harder to replace those references in the formula.
Finding All the #REF! Errors
With the help of Excel’s FIND option, you can find and select the cells in which you have the #REF! Error.
- Open the Find and Replace using Crtl + F.
- Enter the value #REF! in the “Find What”.
- Click on the “Options” button.
- Select “Values” from the “Look in”.
- Click on the “Find All” button.
After that, you can go to each cell and fix the formula.