Why #REF! occur And How to Fix #REF! in Excel

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.

#REF! Error due to copy & paste formula from other cell

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.

#REF! Error due deletion of row, column or sheet

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.


Now delete the A3:A8 cells.

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 correct a #NUM! Error

How to use wildcards in excel

How to Remove Text in Excel Starting From a Position

Validation of text entries

Create drop down list in excel with colour

Remove leading and trailing spaces from text in Excel

Popular Articles

Edit a dropdown list

Absolute reference in Excel

If with conditional formatting

If with wildcards

Vlookup by date

Join first and last name in excel

Count cells which match either A or B

Convert Inches To Feet and Inches in Excel 2016

50 Excel Shortcut to Increase Your Productivity

Leave a Reply

Your email address will not be published. Required fields are marked *

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.