#VALUE! Error in Excel

Error is a common and integral part of Excel formulas and functions, but fixing those errors is what makes you a pro in Excel. As a beginner, finding those errors and restoring them to work correctly is not easy. Every error occurs only because of a user’s mistakes. So, it is essential to know our mistakes and why those errors are happening. In this article, we will show you why we get #VALUE! Errors in Excel and how to fix them.

For example, the simple formula may return as the #VALUE! Error if a space character in any cell is created to clear a cell. To fix the error, the cell which contains a space character needs to be selected and then pressed the Delete key.

Case #1

#VALUE!: This excel error occurs for multiple reasons depending upon the formula we use. The most common reason for this error is the wrong data type used in the cell references.

Example

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, and we have got the result of #VALUE! Error. In cell A5, we have a value as “Forty,” which is the wrong data type, so it returns #VALUE!

To get the correct sum of these numbers, we can use the SUM function in excel.

We get the following result.

The SUM function has ignored the wrong data type in cell A5, adding the remaining cell values and giving the total.

Otherwise, we can change the text value in cell A5 to get the correct result.

For the earlier formula, we have only changed the A5 cell value to 4,000. So now, our previous function is working properly.

Case #2

Now, we will see the second case of #VALUE! Error in Excel formulas.

Example

• 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. In both 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 of 0%.

• Result 3 says B4/A4. It is also the same case as Result 2.

• We have got an #VALUE! Error, so curious case.

• The main reason for this error is that the empty cell is not truly blank because there could be an errant space character.

In cell B4, we have a space character that is not visible to naked eyes. However, it is the reason why we have got #VALUE! Error.

We can use the LEN Excel Function or ISBLANK Excel function to deal with these unnoticeable space characters.

• LEN function will give the number of characters in the selected cell. LEN considers a space character as a single character.

Look at the above function. We have got one character resulting in cell B4, 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 the B4 cell. So, we can conclude that cell B4 is not an empty cell.

Case #3

Another case of resulting #VALUE! Excel error is because the function argument data type is wrongly stored.

Example

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 two cells got the result, but we have an error result of #VALUE!

The end date in the B4 cell has the value of NA, the non-date value, resulting in #VALUE!

• We need to enter the proper date value in cell B3 to correct this error.

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 the #VALUE! Error. In cell C4, a date is not a date stored as a text value, so we need to convert this to a proper date format to get the correct result.

• Other error values are explained in separate articles. This article is dedicated to #VALUE! Error.
• The #VALUE! Error occurs for multiple reasons. We have listed above all the possible scenarios of this error.