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 correctly. Every error occurs because of user’s mistakes only, so it is essential to know what our mistake is and why those errors are happening. 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 errorExcel ErrorErrors in excel are common and often occur at times of applying formulas. The list of nine most common excel errors are - #DIV/0, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!, #####, Circular Reference. 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 used in the cell references.
Follow the steps to fix value error in the excel.
- Look at the below formula for adding different cell values.
In the above basic excel formulaBasic Excel FormulaThe term "basic excel formula" refers to the general functions used in Microsoft Excel to do simple calculations such as addition, average, and comparison. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel are the top ten excel formulas and functions., 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 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 is 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 empty cell 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.
To deal with these unnoticeable space characters, we can use the LEN Excel FunctionLEN Excel FunctionThe Len function returns the length of a given string. It calculates the number of characters in a given string as input. It is a text function in Excel as well as an inbuilt function that can be accessed by typing =LEN( and entering a string as input. or ISBLANK Excel function.
- 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 one character as a result in cell B4, so which confirms B4 cell is not an empty cell.
- Similarly, the ISBLANK function shows TRUE if the cell is empty; otherwise, it shows FALSE.
Look at the above result, we have got FALSE as a 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 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 the 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.
Even though we have a date in cell B4, we still have got the #VALUE! Error, because in cell C4, a date is not date stored as a text value, so we need to convert this to a proper date format to get the correct result.
Things to Remember Here
- Other error values are explained in separate articles, and this article dedicatedly for #VALUE! Error.
- The #VALUE! 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 #VALUE! Error. Here we discuss how to fix a value error in excel with examples and a downloadable excel template. You may learn more about excel from the following articles –