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.

**Table of Contents**hide

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