How To Fix The Value In Excel Formula

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

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:

                                    =C3+C4              // returns #VALUE!

#VALUE! error example - unexpected text value

One option to fix is to enter the missing value in C3. The formula in F3 then works correctly:

#VALUE! error example - unexpected text value FIX 1

Another option in this case is to switch to the SUM function. The SUM function automatically ignores text values:

                                    =              SUM              (C3,C4)              // returns 4.5

#VALUE! error example - unexpected text value FIX 2

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:

#VALUE! error example - errant space character

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:

#VALUE! error example - errant space character in formula bar

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:

#VALUE! error example - function argument not expected type

Below, when proper date is entered in C3, the formula works as expected:

#VALUE! error example - function argument not expected type - FIX

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):

#VALUE! error example - date stored as text