How to Fix the #VALUE! Error in Excel
The #VALUE! error means a formula is trying to do math with something that is not a number — usually text or a stray space in a cell. The fix is to find the offending cell and clean it, or use a function that ignores text. Here is how.
#VALUE! is Excel's way of saying "I can't do this math." It almost always means a formula ran into text, a space, or the wrong kind of value where it expected a number.
What causes #VALUE!
- Adding or multiplying a cell that contains text, like =A2+B2 where B2 holds "n/a".
- A number stored as text, often with a hidden space in front of it.
- A date entered in a format Excel does not recognize.
- A function given the wrong type of argument.
Step 1: Find the cell causing it
Click the cell with the error and look at the formula. Check every cell it references for text or spaces. A quick test: type =ISNUMBER(B2) next to a suspect cell — if it returns FALSE, that cell is text, not a number.
Step 2: Clean or work around it
- Delete stray spaces, or wrap references in TRIM to strip them: =TRIM(B2).
- Convert text-numbers back to numbers (Data > Text to Columns > Finish is a fast fix).
- Use a function that ignores text: replace =A2+B2+C2 with =SUM(A2:C2), which skips text cells.
A #VALUE! often appears inside a larger formula, such as an IF function. A different error, #REF!, has its own cause and cure — see how to fix the #REF! error.
Frequently asked questions
Why does SUM work but + gives #VALUE!?
SUM ignores cells that contain text, while the + operator does not — it tries to add the text and fails. Switching from A2+B2+C2 to SUM(A2:C2) is often the quickest fix.
How do I find every #VALUE! at once?
Press Ctrl+F, search for #VALUE! and set "Look in" to Values. Excel lists every cell showing the error so you can work through them.