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