How do you fix #VALUEErrors in Excel?
You will often encounter the #VALUE error when you work in Excel formulas.
Although the error is generic and does not tell you what the problem might be, it is most commonly caused by a formula that uses a data type other than expected. (Examples below).
Excel will throw an error if you add numbers to Excel and then try to add text values in the same formula. Excel assumes that numbers can only be used for addition or subtraction.
Although it is possible to fix the #VALUE error by making sure the data type are correct in your formulas, this may not always be possible.
How to fix #Value Excel Error
#VALUE! #VALUE! This error can be caused by incorrect data types in cell references.
To fix Excel’s value error, follow the steps below.
Use the formula below to add different cell values.
The basic Excel formula above allows us to add numbers A2 through A6 in A7. We get the #VALUE result! Error! In cell A5, there is a value of “Forty” which is not the correct data type. It returns #VALUE!
To find the sum of the numbers, you can use the SUM function within excel.
The following result is obtained.
The SUM function ignored cell A5’s wrong data type and added the remaining values to the total.
We can also change the text value of cell A5 to obtain the correct result
We have changed the A5 cell value from 3,000 to 4,000 for the previous formula. Our previous function now works properly.
We will now see the second #VALUE case! Excel formulas are prone to errors.
Take a look at the formula below.
We have divided column A with column B. We have three results.
Result 1 isB2/A2. We have two cells with numerical values. The result is 80%.
Result 2says A3/B3. We have 0 because cell B3 has no value.
Result 3says A4/A4. The same applies to Result 2.
We have a #VALUE! Error, so curious case.
This error could be caused by an errant spacing character.
Cell B4 has a space character that is invisible to naked eyes. It is why we have #VALUE! Error.
To deal with these non-noticeable spaces characters, we can use either the LEN Excel Function oder ISPLANK Excel function.
LEN function will show you the number of characters found in the selected cell. LEN treats a space character like a single character.
Take a look at the function above. One character has resulted in cell B4, which proves that B4 cell isn’t empty.
The ISBLANK function also shows TRUE when the cell is empty. It will display FALSE otherwise.
Take a look at the result. As a result, we have FALSE for the cell B4. We can conclude that B4 cell is not empty.
Function argument not expected type
#VALUE! Function arguments that are not expected types can also cause error. The NETWORKDAYS function calculates the number of workdays that have passed between two dates. Cell C3 contains the date “apple”, which is invalid. The NETWORKDAYS function cannot compute working days, and returns the #VALUE error:
If the correct date is entered into C3, then the formula will work as expected.
dates saved as text
Sometimes dates in a worksheet are incorrectly stored as text. The EDATE function can be used to calculate the expiration date for three months following a purchase date in the example below. The #VALUE is returned by the formula in C3. Error because B3 stores the date as text (i.e. Not properly recognized as date
The error will be fixed when the date in B3 has been changed.
This page offers some solutions if you need to fix multiple dates that were stored in text.