REF Error in Excel (9 Suitable Examples)

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.

dataset


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.

REF ERROR

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.

GO TO SPECIAL

➤ After that, Go to Special window will appear.  First, select Formulas and check on Errors. After that click on OK.

GO TO SPECIAL BOX

 

Now, you will see all the cells with REF error in your dataset will be selected.

REF ERROR IN EXCEL

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.

FIND AND REPLACE

Now, the Find and Replace window will appear.

➤ In the Find what box type #REF! and click on Replace All.

FIND AND REPLACE BOX

After that, a confirmation box will appear showing the number of replacements.

➤ Press OK in this box and close the Find and Replace box.

CONFIRMATION 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.

REMOVING REF ERROR

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.

RANGE REFERENCE

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.

REF ERROR

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.

REF ERROR


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.

VLOOKUP

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.

REF ERROR IN EXCEL

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.

VLOOKUP

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.

REMOVING REF ERROR


Similar Readings


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.

HLOOKUP

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.

REF ERROR IN EXCEL

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.

HLOOKUP

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.

REMOVING REF ERROR IN EXCEL


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.

INDEX FUNCTION

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.

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.

INDEX

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.

REMOVING REF ERROR IN EXCEL


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.

NEW WORKBOOK

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.

REF ERROR

➤ Now open the workbook Jennifer and insert the same formula again.

VALUE

This time, it won’t show REF error anymore and will give the value from Jennifer workbook.

REMOVING REF ERROR


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.

IFERROR

Now, if we delete one of the columns the formula will no longer show the error sign. Rather it will show the text “Incomplete”.

REPLACING REF ERROR

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.


Related Articles

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo