In Excel, the #VALUE! the error occurs when the type of the input value is not correct. Let’s say you want to sum two numbers from two cells, but in the second cell, the value is a text. So here performing this calculation is not possible because you can’t sum a number or a text.
Steps to Fix #VALUE! Error in Excel
- The first step to fixing the #VALUE! error is to check out the values that you have specified in the formula are in the right type.
- Check if the value you have specified in an argument is valid or not. For Example, if I want to extract the 25th character from a text but that text has only 20 characters.
- After that, make sure to check the dimension of the arrays you have referred to. For example, in one range you have 10 cells, and in the second cell, you have 12 cells. This also leads to the #VALUE! error.
- Check if any value in your input data has a #VALUE! error already. This can also lead to having a #VALUE! error in the result.
Above are some of the initial checks that you can perform to fix the error. Now ahead we will look at a few real-life examples to understand it further.
#VALUE! Error With SUMPRODUCT
In the following example, we have used the SUMPRODUCT function and in the second array we have referred to the range with ten cells, and in the second range with have mistakenly referred to the B1:B9 which has 9 cells. And this leads to having a #VALUE!
This can happen with other functions too, like, SUMIF, SUMIFS, AVERAGEIF, and AVERAGEIFS.
#VALUE! Error While Working Dates
In the following example, we have a date in cell A1 that is stored as text and in the wrong format. Now, if we try to use this date in a calculation this will return #VALUE!
The reason is when you try to do a calculation and that date is a test, that function or even formula, can’t identify that date, even though that format of date is readable by the user.
#VALUE! When Including a Space Value in a Calculation
In the following example, when I was trying to add cells A1 and A2, I got the #VALUE! error.
The reason for this is: In cell A1 we have a space that is invisible for any user to see. And when I referred to it in the calculation it gave me #VALUE!
So are some of the reasons why you might face an #VALUE! error in Excel. But the best way to deal with this error is to have a look at the values that you have referred to in the formula.