The #VALUE! error means a formula received the wrong type of data — usually text where it expected a number. It's Excel's way of saying "I can't do math on this." Here's how to track down and fix it.
What #VALUE! means
#VALUE! appears when a formula expects one kind of value but gets another. The most common case: a math operation runs into text instead of a number. Excel can't add a word to a number, so it returns #VALUE!.
The most common causes
Text where a number should be. If a cell contains text — even a number stored as text, or a stray letter or space — arithmetic on it fails. =A1+B1 returns #VALUE! if B1 contains "N/A" or " " instead of a number.
Hidden spaces or characters. A cell that looks like it contains 100 might actually contain "100 " with a trailing space, or a non-breaking space imported from a web page. These make Excel treat it as text.
Numbers stored as text. Data imported or pasted from other systems often arrives as text. It looks numeric but isn't, and math on it errors. A tell-tale sign is a small green triangle in the cell's corner and left-alignment (text left-aligns; numbers right-align by default).
Wrong argument type in a function. Passing text to a function that expects a number, or a range of the wrong shape, can also trigger it.
Date arithmetic on text dates. Dates stored as text rather than real date values cause #VALUE! when you try to calculate with them.
How to fix it
Find the offending cell. Check each cell your formula references for anything that isn't a clean number. Look for text, spaces, or letters hiding in supposedly numeric cells.
Convert text to numbers. If cells contain numbers stored as text:
- Select them, click the warning triangle, and choose "Convert to Number," or
- Multiply by 1 or add 0 in a helper column (
=A1*1), or - Use the VALUE function:
=VALUE(A1)converts text to a number.
Remove hidden spaces. Use TRIM to strip extra spaces and CLEAN to remove non-printing characters:
=TRIM(A1)
For stubborn non-breaking spaces from web data, you may need SUBSTITUTE to remove them specifically.
Use functions that ignore text. SUM ignores text cells rather than erroring, so =SUM(A1:A10) works even if some cells contain text — unlike =A1+A2+.... Switching from manual addition to SUM often sidesteps #VALUE! entirely.
Suppressing it (carefully)
If some text in the range is expected and harmless, wrap the formula in IFERROR:
=IFERROR(A1+B1, "")
But only after you've confirmed the text is supposed to be there — otherwise you're hiding a data problem.
The takeaway
#VALUE! is almost always a data-type mismatch: text where a number belongs. Hunt down the non-numeric cell, convert it to a real number (VALUE, or multiply by 1), and clean out hidden spaces with TRIM. Fix the data, and the error disappears.