What is #Value! Error in Excel?
Error is a common and integral part of excel formulas and function, but fixing those errors is what makes you a pro in excel. As a beginner, it is not easy to finding those errors and fixing them to work properly. Every error occurs because of user’s mistakes only, so it is important to know what our mistake is and why those errors are occurring. In this article, we will show you why we get #VALUE! errors in excel and how to fix it.
How to Fix #Value! Error in Excel?
#VALUE!: This excel error occurs due to multiple reasons depending upon the formula that we use but the most common reason for this error is the wrong data type of values used in the cell references.
- Look at the below formula for adding different cell values.
In the above basic excel formula, we are trying to add numbers from A2 to A6 in cell A7 cell and we have got the result of #VALUE! error, the reason for this in cell A5 we have got value as “Forty” which is the wrong data type so returns #VALUE!.
- To get the correct sum of these numbers we can use the SUM function in excel.
- We get the following result.
SUM function has ignored the wrong data type in the cell A5 and adds remaining cell values and gives the total.
- Otherwise, we can change the text value in cell A5 to get the correct result.
For the earlier formula only we have changed the A5 cell value to 4000 and now our previous function working fine.
Now we will see the second case of #VALUE! error in excel formulas.
- Look at the below formula.
We have divided column B with column A and we have got three different results.
- Result 1 says B2/A2, and in both the cells we have numerical values and the result is 80%.
- Result 2 says B3/A3 since there is no value in cell B3 we have got the result as 0%.
- Result 3 says B4/A4, this is also the same case of Result 2.
- We have got an #VALUE! error, so curious case.
- The main reason for this kind of error is because of the cell which is empty is not truly blank at all because there could be an errant space character.
In cell B4 we have space character which is not visible at all for naked eyes. This is the reason why we have got #VALUE! error.
- LEN function will give the number of characters in the selected cell and LEN considers space character as a single character.
Look at the above function we have got 1 character as the result in cell B4, so which confirms B4 cell is not an empty cell.
- Similarly, the ISBLANK function shows the result as TRUE if the cell is actually an empty cell otherwise it shows the result as FALSE.
Look at the above result we have got FALSE as the result for B4 cell so we can conclude that cell B4 is not the empty cell.
Another case of resulting #VALUE! Excel Error is because of function argument data type is wrongly stored.
Look at the below image.
- In the above formula example, we have used the NETWORKDAYS Excel function to find the actual working days between two dates.
- The first 2 cells got the result but in the last cell, we have got an error result of #VALUE!.
The reason for this is the end date in the B4 cell has the value of NA, which is the non-date value, so resulting in #VALUE!.
- To correct this error we need to enter proper date value in cell B3.
There is also another chance of getting the same error even though we have the date value.
- For example, look at the below cell.
Look even though we have a date in cell B4 still we have got the #VALUE! error, because in cell C4 date is not actually date which is stored as a text value, so we need to convert this to proper date format to get the correct result.
Things to Remember Here
- Other error values explained in separate articles and this article dedicatedly for #VALUE! Error.
- #VALUE! The error occurs due to multiple reasons, above we have listed out all the possible scenarios of this error.
This has been a guide to excel error in value. Here we discuss how to fix a value error in excel with examples and downloadable excel template. You may learn more about excel from the following articles –