How To Solve #Value Error In Excel
How To Solve #Value Error In Excel

How To Solve #Value Error In Excel

How To Solve #Value Error In Excel

How To Solve #Value Error In Excel happens due to multiple causes depending upon the formula that we use, but the most general cause for this error is the incorrect data type used in the cell references.

Follow the steps to resolve #value error in the excel.

How to Solve the #VALUE! error

The #VALUE! error arises once a value is not the expected type. This can happen when cells are left blank, once a function that is expecting a number is given a text value, and when dates are evaluated as text by Excel. Resolving a #VALUE! error is generally just a matter of entering the right kind of value.

The #VALUE error is a bit tricky because some functions spontaneously ignore invalid data. For example, the SUM function just ignores text values, yet regular addition or subtraction with the plus (+) or minus (-) operator will return a #VALUE! error whether any values are text.

  1. Look at the below formula for adding different cell values.

In the previous basic excel formula, we are trying to add numbers from A2 to A6 in cell A7 cell, and we have got the outcome of #VALUE! Error, the cause for this in cell A5, we have value as “Forty”, which is the wrong data type so returns #VALUE!.

  1. To get the right sum of these numbers, we can use the SUM function in excel.
  1. We get the following result.

SUM function has ignored the incorrect data type in the cell A5 and adds remaining cell values and gives the total.

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

For the earlier formula, only we have changed the A5 cell value to 4000, and currently our above function is working fine.

Case #2

Initially 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 outcomes.

  • Result 1 says B2/A2, and in both the cells, we have numerical values, and the outcome is 80%.
  • Result 2 says B3/A3 since there is no value in cell B3, we have got the outcome as 0%.
  • Result 3 says B4/A4. This is also the similar case of Result 2.
  • We have got an #VALUE! Error, so weird case.
  • The main cause 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 seen at all for naked eyes. This is the reason why we have got #VALUE! Error.

To deal with these unnoticeable space characters, we can utilize the LEN Excel Function or ISBLANK Excel function.

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

Look at the previous function, we have got one character as a outcome in cell B4, so which confirms B4 cell is not an empty cell.

  • Similarly, the ISBLANK function appears TRUEif the cell is empty; otherwise, it displays FALSE.

Look at the previous result, we have got FALSE as a result for B4 cell, so we can conclude that cell B4 is not the empty cell.

Case #3

Another case of resulting #VALUE! Excel Error is because of function dispute data type is incorrectly stored.

Example

Look at the below image.

  • In the previous formula example, we have used the NETWORKDAYS Excel functionto find the exact working days between two dates.
  • The first 2 cells got the outcome, but in the last cell, we have an error result of #VALUE!.

The cause for this is the last date in the B4 cell has the value of NA, which is the non-date value, so resulting in #VALUE!.

  • To incorrect this error, we require to enter the proper date value in cell B3.

There is also another chance of getting the similar error even though we have the date value.

  • For example, look at the under cell.

Yet 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 require to convert this to a exact date format to get the right result.

What is #VALUE error in Excel?

Once you enter unexpected data in a formula, it might display a #VALUE! error. This Excel error can happen because of one of the following causes:

  • Text is used in Arithmetic Operations; or
  • Cell contains hidden spaces; or
  • Date stored as text

Let’s consider different examples to examine each of these reasons and learn how to resolve them!

#VALUE is Excel’s method of saying, “There’s something wrong with the way your formula is typed. Or, there’s something incorrect with the cells you are referencing.” The error is very general, and it can be hard to examine the exact cause of it. The information on this page shows common issues and solutions for the error. You may require to try one or more of the solutions to resolve your particular error.

Things to Remember Here

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

    Conclusion

    Value error in Excel happens when the value provided in the formula is not the expected type. And, when you fix that value the error will disappear.

    Click here to learn about the Top 20 general Errors that you may face while working on Excel.

Microsoft Excel Support