Prantick Bala
Reference Error or REF Error occurs in Excel when a formula refers to invalid cells. It can happen when you delete cells, rows, or columns which are used in a formula. In the case of reference error, Excel shows #REF! error sign. In this article, we’ll show you how REF errors can occur in Excel and how you can deal with the error.
Consider the following dataset. Here quarterly and yearly sales data of different salesmen are given. The yearly sales data are found by summing up all the quarterly sales data. Now using this dataset, we’ll show you how REF error can happen in Excel and how you can get rid of the error.
Download Practice Workbook
You can download the workbook from the link below
Examples of Dealing REF Error in Excel
1. REF Error from Deleting a Cell, Column or Row
If we delete a cell, column or row which is used in a formula, Excel will show REF error in the formula cell. Let’s see if we delete Quarter 4 sales (column E) from our dataset, what will happen.
As a result of deleting the Quarter 4 sales column, now the cells of the Yearly sales column are showing REF error. This is happening because now the formula in this column can’t find one of the referred columns. If we select any cell from the formula column we can see from the formula bar that one of the referred cells is showing #REF! Sign. As we have deleted the column of a referred cell of the formula, now the formula can’t find the cell and is showing REF error.
Read More: How to Fix #REF! Error in Excel (6 Solutions)
2. Finding Out the Cells with REF Error
If you have a very long dataset and a lot of formulas in your dataset, finding out the REF errors manually can be tiresome. But it is necessary to find out all of the REF errors so that you will be able to solve the errors.
➤ To find out all of the errors at a time first select your entire dataset and go to Home > Editing > Find & Select > Go to Special.
➤ After that, Go to Special window will appear. First, select Formulas and check on Errors. After that click on OK.
Now, you will see all the cells with REF error in your dataset will be selected.
Read More: How to Find Reference Errors in Excel (3 Easy Methods)
3. Removing Multiple REF Errors
You can remove all the REF Errors from your Excel dataset by using Find and Replace feature. ➤ First, select your entire dataset and go to Home > Editing > Find & Select > Replace.
Now, the Find and Replace window will appear.
➤ In the Find what box type #REF! and click on Replace All.
After that, a confirmation box will appear showing the number of replacements.
➤ Press OK in this box and close the Find and Replace box.
As a result, you will see, there is no more REF error in your dataset. The formula is showing the value exempting the deleted columns.
If you click on any cell of the formula column you can see from the formula bar that the #REF! the sign is removed and the formula is calculating the value only considering the existing cells.
Read More: How to Remove Value Error in Excel (4 Quick Methods)
4. Range Reference to Avoid REF Error
Instead of referring to the cells with commas as the relative references, you can use a range reference to avoid REF error. In the previous cases, we have used the following formula in cell F6, =SUM(B6,C6,D6,E6). Now we will use range reference to find out the summation in column F.
➤ Type the following formula in cell F6,
=SUM(B6:E6)Here, the formula will use cell range B6:E6 as the reference and will give the summation in cell F6. Drag cell F6 to the end of your dataset, so the formula will be applied to all the cells in column F.
Now if you delete one of your columns which are used in the formula, you will see that the REF error won’t be shown this time. In this case, the formula will calculate the value omitting the values of the deleted column.
Read More: VALUE Error in Excel: 7 Reasons with Solutions
5. VLOOKUP Function REF Error
If you insert an incorrect column index number in the VLOOKUP function Excel will show REF error. Suppose for our dataset we want to find yearly sales of different salesmen. So we have typed the following formula in an empty cell, =VLOOKUP(H8,A4:F12,7,FALSE).Here, H8 is the lookup value (Harold), A4:F12 is the table array. 7 is the column index number and FALSE indicates that the formula will return an exact match.
In our formula, we have given 7 as the column index number. But the table array is A4:F12 which has only 6 columns. As a result, the formula will return a REF error.
Let’s correct the formula.
➤ Type the following corrected formula,
=VLOOKUP(H8,A4:F12,6,FALSE)Here, H8 is the lookup value, A4:F12 is the table array. 6 is the column index number and FALSE indicates that the formula will return an exact match.
Now at this time, the column index number 6 lies within the table array. So the formula won’t show REF error this time; rather it will return the yearly sales of the salesman whose name is in cell H8.
Read More: Errors in Excel and Their Meaning (15 Different Errors)
Similar Readings
- How to Fix “Fixed Objects Will Move” in Excel (4 Solutions)
- Excel VBA: Turn Off the “On Error Resume Next”
- [Fixed] Excel Print Error Not Enough Memory
- On Error Resume Next: Handling Error in Excel VBA
- Reasons and Corrections of NAME Error in Excel (10 Examples)
6. HLOOKUP Function with Error in Reference
If you insert an incorrect row index number in the HLOOKUP function Excel will show REF error. Suppose for our dataset we want to find total sales of different quarters using HLOOKUP. So we have typed the following formula in an empty cell,=HLOOKUP(H8,B5:F12,9,FALSE)Here, H8 is the lookup value, B5:F12 is the table array. 9 is the ROW index number and FALSE indicates that the formula will return an exact match.
In our formula, we have given 9 as the row index number. But the table array is B5:F12 which has only 8 rows. As a result, the formula will return a REF error.
Let’s correct the formula.
➤ Type the following corrected formula,
=HLOOKUP(H8,B5:F12,8,FALSE)Here, H8 is the lookup value, B5:F12 is the table array. 8 is the row index number and FALSE indicates that the formula will return an exact match.
Now at this time, the row index number 8 lies within the table array. So the formula won’t show REF error; rather it will return total sales in Quarter 3.
Read More: [Fixed] Excel Found a Problem with One or More Formula References in This Worksheet
7. INDEX Function with Incorrect Reference
If you insert an incorrect row or column number in the INDEX function Excel will show REF error. Let’s say, for our dataset we want to find total yearly sales. So we have typed the following formula in an empty cell, =INDEX(B6:F12,7,6)Here, B5:F12 is the array. 7 is the row number and 6 is the column number.
In our formula, we have given 6 as the column number. But the array is B5:F12 which has only 5 columns. As a result, the formula will give a REF error.
Let’s correct the formula.
➤ Type the following corrected formula,
=INDEX(B6:F12,7,6)Here, B5:F12 is the array. 7 is the row number and 5 is the column number.
Now at this time, column number 5 lies within the array. So the formula won’t show REF error; rather it will give the value of the total yearly sales.
Read More: [Fixed!] NUM Error in Excel (4 Reasons with Solutions)
8. Reference Error in INDIRECT Function
In the time of importing data from another workbook with the INDIRECT function, if the workbook from where the data will be imported is closed, Excel will give a REF error. Suppose we want to import sales data of a salesman named Jennifer from the workbook named Jennifer.
Now, without opening the workbook Jennifer we have typed the following function in our current workbook,
=INDIRECT(" '[Jennifer.xlsx]"&H10&"'!$B$6")Here,Jennifer.xlsx is the workbook from where we want to import data, H10 is the sheet name, SALES_DATA of Jennifer.xlsx workbook. and $B$6 is the cell of the sheet SALES_DATA of Jennifer.xlsx workbook.
But the formula won’t import data from the workbook. It will show a REF error.
➤ Now open the workbook Jennifer and insert the same formula again.
This time, it won’t show REF error anymore and will give the value from Jennifer workbook.
Read More: How to Correct a Spill (#SPILL!) Error in Excel (7 Easy Fixes)
9. Enter Custom Text Instead of REF Error with IFERROR Function
We can remove the REF error from our worksheet and can show custom text in the place of this error using the IFERROR function. Consider the first example where we got the formula column with #REF! sign because of deleting one column. Now with the IFERROR function, we will show the text Incomplete in the place of those error signs.
➤ First, type the following formula in the first cell of the column, press ENTER, and drag the cell to the end to apply the formula in all cells.
=IFERROR(SUM(B6,C6,D6,E6), "Incomplete")The formula will give the summation if no error occurs.
Now, if we delete one of the columns the formula will no longer show the error sign. Rather it will show the text “Incomplete”.
Read More: Excel Error: The Number in This Cell is Formatted as Text (6 Fixes)
Conclusion
In this article, we have tried to give you basic ideas of how REF error occurs in Excel and how you can deal with such errors. Hope now you can solve the problem of reference error in Excel. If you face any confusion please feel free to leave a comment.
