#VALUE! in Excel: the wrong kind of thing in the formula
Why plus fails where SUM works, the non-breaking space that TRIM cannot remove, and how to find the cause of a #VALUE! in under a minute.
#VALUE! means the formula was handed the wrong kind of thing. It
wanted a number and it got text.
Most of the confusion around it comes from one fact that nobody writes down. Arithmetic is strict. The SUM family is not. Once you know that, the error stops being mysterious.
The same data, two different answers
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Item | Qty | Price | Total |
| 2 | Tube | 3 | 4.50 | 13.50 |
| 3 | Chain | 1 | n/a | #VALUE! |
| 4 | Pad | 2 | 12.00 | 24.00 |
C3 holds the text "n/a". Now look at what two formulas do with column C.
=C2+C3+C4 → #VALUE!
=SUM(C2:C4) → 16.50
SUM walks a range and skips anything that is not a number. Plus does not. Plus takes what it is given and tries to add it.
This is why a column can total correctly in one cell and fail in the cell below it. The data did not change. The formula did.
Text that looks like a number is fine
="5"+1 returns 6. Excel converts text into a number when the text
is a number. It only gives up when the text is not one.
So "5" is fine, "5.5" is fine, and "5 units" is #VALUE!. That last one is the shape most real data takes.
The space that is not a space
Copy a number from a web page and you often get 1 234. The gap is a non-breaking space. It is character 160, not character 32.
It looks exactly like a normal space. It is not one. And this is the part that costs people hours: TRIM does not remove it. TRIM removes character 32 and nothing else.
Find it: =CODE(MID(A1,2,1)) on the character
you suspect. 160 is the non-breaking space. 32 is a normal one.
Remove it:
=VALUE(SUBSTITUTE(A1,CHAR(160),""))
CLEAN does not help either. CLEAN removes characters 0 to 31, the unprintable ones. 160 is above that range.
Dates that are really text
=B1-A1 on two dates gives the number of days between them. On two
dates that are text, it gives #VALUE!.
Dates arrive as text from almost every export. The test is the same as for numbers. A real date sits on the right of its cell. A text date sits on the left.
Fix one cell: =DATEVALUE(A1), then format the
result as a date.
Fix a column: select it. Then Data → Text to Columns → Next → Next → Date. Pick the order the text is in. This is faster than any formula and it changes the cells themselves.
A range where one cell was expected
=LEFT(A1:A5,3) in an older Excel returns #VALUE!. LEFT wants one
piece of text and it was handed five.
In Excel 365 the same formula spills into five cells instead. Are you on an older version? A formula copied from the internet that returns #VALUE! is often this.
How to find the cause in under a minute
- Click the cell. Click the small warning triangle. Excel often names the problem itself.
- Select part of the formula in the formula bar and press F9. Excel shows what that part evaluates to. Do it piece by piece until one piece is not what you expected. Press Escape when you are done, never Enter.
-
Put
=ISNUMBER(C3)beside the suspect cell. FALSE on something that looks like a number is the answer. -
Put
=LEN(C3)beside it. A length longer than what you can see means hidden characters.
What not to do
Do not wrap it in IFERROR. =IFERROR(C2+C3+C4,0) turns a visible problem into a total that is too low, with nothing to show it. The error was doing its job.
Clean the data instead. If the data cannot be cleaned, switch to SUM, which skips text on purpose and says so.
Questions people ask
Why does SUM work but plus does not?
SUM is built to walk a range of mixed content and add the numbers in it. Plus is an operator on two values. Excel decided long ago that the first should be forgiving and the second should not.
My cell looks empty but the formula says #VALUE!
It is not empty. It holds a space, or a non-breaking space. Or an empty text string left by a
formula like =IF(A1>0,A1,""). Check with
=ISBLANK(). A truly empty cell answers TRUE.
Is #VALUE! the same as #N/A?
No. #VALUE! means the wrong kind of thing went in. #N/A means a lookup found nothing. Different causes, different fixes. The #N/A article covers the other one.
Does Excel for the web behave the same way?
Yes for all of the above. Text to Columns is the one fix that is easier in the desktop version. The formulas work everywhere.