In this tutorial, we will know what is #VALUE error in Excel at the same time why and when do we get this error in our calculations.
Error is a common and necessary part of Excel formulas and functions, but fixing those errors is what distinguishes you as an Excel expert.
Definitely, as a beginner finding those errors and restoring them to work properly again is a little bit hard and tricky. However, every error is the result of the user’s error. The reason why it is significant to understand why this occurs.
What is #value error in Excel
Excel #Value error occurs when the value return is not the expected type.
Particularly this error pops up when the formula anticipates a reference to be a number however the reference we have contains characters, spaces or text.
To put it simply, “there is something wrong with the formula you type or there is something wrong with the value or cell you are referencing“.
Since #value error is general it is difficult to find the exact cause of why this error was raised in a worksheet.
Moving on why are we getting this error?
#🅥🅐🅛🅤🅔!
Why am I getting value Error in Excel?
We often get the #value error in excel because we enter wrong type of data. This would be a little bit tricky because some function just ignores text value. Meanwhile, operations such as addition(+) and subtraction (-) particularly will return #value error if the value is text or characters.
When is the value error raised in Excel?
Perhaps, you already have an idea when this error appears.
Literally, this #value error appears when the value encounter is spaces, text, or characters in a formula while expecting numbers. Additionally, even when dates are read as text by Excel.
Luckily we have ways to fix this problem. Always check your formula and make sure that the reference cell is correct. When the formula is correct, check the cell itself if the characters are mistaken.
Apparently, Excel distinguishes text from numerical value the reason why adjusting cells to have numerals instead of text can also solve this error.
How to Fix #VALUE error in Excel
Time needed: 2 minutes
Fixing the #VALUE error is a bit tricky, but we provided procedures here to consider in fixing this error.
- Check the type of value in the formula.
The first thing we have to do is to check out the specified value in the formula if it is the right data type.
- Check if the argument value is valid or not.
This time check whether the value of the argument is valid or not. For instance, an argument requires an index column number from the range of cells but the reference specified is text character.
- Check the length of arrays.
The next thing you have to do is to check the reference arrays dimension. For instance, the first range consists of 10 cells while the second cell reference has 15. Hence, lead to #Value! error.
- Check the data if has #VALUE error already.
Secure the input data if any case has already #VALUE! error. This can also lead #VALUE! error on the result.
#VALUE! Space Value in a Calculation
In this example, we will know how Space value return #Value! error in calculation.
So in our example, we are trying to add cells L2, L3, and L4. However, we got #VALUE! error.
The reason why is it has space on an empty cell in L3 which apparently we cannot see. And when we included it in calculation it result to an error.
In fact, these would be some of the instances you may consider when encountering #Value! error.
Value error in Excel XLOOKUP
When you are having #VALUE! error in XLOOKUP, most probably the lookup array is not dimension to the return Array.
In our example our arrays are in not the same dimension, the look-up array only consists of 9 rows while the return array exceeds and has 12.
As you can see our return array exceeds one row that we did not notice and lead to #Value Error. To fix it just change the cell range to A3: H11.
Conclusion
That’s all we need to know about #VALUE! error in Excel. So in this tutorial, we covered the possible reason along with possible ways to get rid of this error in our worksheet.
Now that you know what cause of this error it is easy for you now to troubleshoot your calculations when you faced this kind of error.
I hope this tutorial is useful in doing your task. Thank you for reading 🙂