In this article we will learn about why #REF! occur And How to Fix #REF! in Excel.

In excel, at times we get #REF! error. This error occurs when there is something important missing with range which can't be identified in the formula you have supplied to the cell and is recognized by Excel by showing the error. As per Microsoft official site a **#REF! **is Excel's way of saying, the range supplied is **not** **valid** in the formula you provided.

If you are using cell reference to array, if it's not in valid, this error suggests you to check some points discussed below. This usually happens when you try to copy the formula to one cell to another cell without realizing the relative reference in Excel.

Let’s understand how this error occur and how to fix them with some examples. First of all check the formula in the cell for each and every character carefully.

The most basic reason of #REF! error is when the formula used in the cell doesn't find the valid reference. Generally Excel has predefined format for the formula which it accepts only if that format is followed. Here #REF! error suggests that array provided as cell reference is not followed by the correct format. See the below example to understand more.

For example, the MIN function finds the lowest value in the array given as input. Here the range (A2:A8) is fed as cell reference to MIN function in A9 cell. And copy of the cell is paste in the D4 cell.

Look in the above snapshot and realize the mistake just happened. The MIN value for the above formula array ( **A2** & **A21 **) .

How to fix it! You can fix the error for the result. Firstly you need to ask yourself, what thing you need to copy the value or the formula. See the below steps to find what do we need to copy.

**Copying the value from one cell to other**

Copy and paste values from one cell to another via paste by values option. The option just allows the user to just paste the value of the cell instead of formula.

In the above gif the range is provided. Follow the below explained steps to avoid errors.

**1. Ctrl + C **to copy the cell from B9 cell.

**2.** Use **Ctrl + Alt + V **to open the multiple options to paste or use the paste special option from the **Home > Paste** as shown below.

**3.** Choose the **values **option to paste only the value to the required cell.

**Copying the formula from one cell to another**

In the gif shown below only the formula is used. Follow the below steps to avoid having #REF! error.

Follow the steps to avoid the #REF error

**a. Ctrl + C **to copy the cell from B9 cell.

**b.** Use **Ctrl + Alt + V **to open the multiple options to paste or use the paste special option from the **Home > Paste** as shown below.

**c. **Choose the normal** paste **option to paste only the formula to the required cell.

**d. **Press enter and your formula is in new cell now

As you can see the **#REF! **Error can be handled when you use correct Excel format while using range in the formula.

Another reason for #REF! error is when the formula used in the cell doesn't find the valid reference of row, column or sheet. Generally Excel has predefined format for the formula which it accepts only if that format is followed. Here #REF! error suggests that array provided as cell reference doesn't find the matched row, column or sheet. See the below example to understand more.

For example, the MIN function finds the lowest value in the array given as input. Here the range (A2:A8) is fed as cell reference to MIN function in A9 cell.

Look in the above gif and realize the mistake just happened. The MIN value for the above formula array ( **A2** & **A21 **).

How to fix it! You can fix the error for the result. Firstly you need to ask yourself, what you need to for the value or the formula. See the below steps to find what do we need to copy.

**Copying the value from one cell to other**

Copy and paste values from one cell to another via paste by values option. The option just allows the user to just paste the value of the cell instead of formula. And then delete the row or column or sheet.

In the above gif the range is provided. Follow the below explained steps to avoid errors.

**a. Ctrl + C **to copy the cell from B9 cell.

**b. **Use **Ctrl + Alt + V **to open the multiple options to paste or use the paste special option from the **Home > Paste** as shown below.

**c,** Choose the **values **option to paste only the value to the required cell.

And then delete the row or column or sheet.

**Copying the formula from one cell to another**

In the gif shown below only the formula is used. Follow the below steps to avoid having #REF! Error. And then delete the row or column or sheet.

Follow the steps to avoid the #REF error

**a. Ctrl + C **to copy the cell from B9 cell.

**b. **Use **Ctrl + Alt + V **to open the multiple options to paste or use the paste special option from the **Home > Paste** as shown below.

**c. **Choose the normal** paste **option to paste only the formula to the required cell.

**d. **Press enter and your formula is in new cell now

As you can see the **#REF! **Error can be handled when you use correct Excel format while using range in the formula.

Hope this article about how Why #REF! occur And How to Fix #REF! error in Excel is explanatory. Find more articles on Excel errors functions here. Please share your query below in the comment box. We will assist you.

**Related Articles**

**#VALUE Error And How to Fix It in Excel**

**How to Remove Text in Excel Starting From a Position**

**Create drop down list in excel with colour**

**Remove leading and trailing spaces from text in Excel**

**Popular Articles**

**If with conditional formatting**

**Join first and last name in excel**

**Count cells which match either A or B**

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.