The #VALUE error is a bit tricky because some functions automatically ignore invalid data. For example, the SUM function just ignores text values, but regular addition or subtraction with the plus (+) or minus (-) operator will return a #VALUE! error if any values are text. The examples below show formulas that return the #VALUE error, along with options to resolve.
Example #1 - unexpected text value
In the example below, cell C3 contains the text “NA”, and F2 returns the #VALUE! error:
One option to fix is to enter the missing value in C3. The formula in F3 then works correctly:
Another option in this case is to switch to the SUM function. The SUM function automatically ignores text values:
Example #2 - errant space character(s)
Sometimes a cell with one or more errant space characters will throw a #VALUE! error, as seen in the screen below:
Notice C3 looks completely empty. However, if C3 is selected, it is possible to see the cursor sits just a bit to the right of a single space:
Excel returns the #VALUE! error because a space character is text, so it is actually just another case of Example #1 above. To fix this error, make sure the cell is empty by selecting the cell, and pressing the Delete key. Note: if you have trouble determing whether a cell is truly empty or not, use the ISBLANK function or LEN function to test.
Example #3 - function argument not expected type
The #VALUE! error can also arise when function arguments are not expected types. In the example below, the NETWORKDAYS function is set up to calculate the number of workdays between two dates. In cell C3, “apple” is not a valid date, so the NETWORKDAYS function can’t compute working days and returns the #VALUE! error:
Below, when proper date is entered in C3, the formula works as expected:
Example #4 - dates stored as text
Sometimes a worksheet will contain dates that are invalid because they are stored as text. In the example below, the EDATE function is used to calculate an expiration date three months after a purchase date. The formula in C3 returns the #VALUE! error because the date in B3 is stored as text (i.e. not properly recognized as a date):
When the date in B3 is fixed, the error is resolved:
If you have to fix many dates stored as text, this page provides some options for fixing.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
