The #VALUE! guide
#VALUE! usually means a calculation met text where it needed a number: "12 kg" typed into a weight column, or a cell that looks empty but holds a space. Find that cell and fix the data; changing + to SUM only hides it.
Find the cell
- Text sits on the left of a cell, numbers on the right. "12 kg" sits on the left.
- Click the cell that shows #VALUE! and look at the cells its formula uses. The error passes along: a total that adds a #VALUE! cell shows #VALUE! too, so start from the first one.
=ISTEXT(B5)returns TRUE for text, including a cell that holds only a space.
+ stops, SUM skips
=B2+B3+B4 stops at "12 kg" with #VALUE!. =SUM(B2:B4) skips it and returns a total without a word of warning, 12 short. The error is the better of the two: it tells you something is wrong. Switching to SUM to make the error go away leaves the wrong number in the report.
Text that is only a number, like "12" pasted from somewhere, is a different case: + turns it into 12, while SUM skips it. See numbers stored as text.
Fix the data
- A unit typed after the number: type the number alone, and put the unit in the header: "Weight (kg)".
- A cell that looks empty: it may hold a space. Select it and press Delete.
- A whole column of "12 kg" values: in a helper column,
=VALUE(SUBSTITUTE(B2," kg","")), then paste the results as values.
Other causes
A date typed as text, like 31.12.2026 in a US-format sheet, gives #VALUE! in date arithmetic. A function given a range where it wants one value can too. The fix is the same idea: find which input isn't what the formula expects.
Common mistakes
- IFERROR around the formula. The cell shows 0 or nothing, and the weight is still missing from the total.
- Retyping the total. It's right until a weight changes.
Other errors: #DIV/0!, #NAME?, #REF!.
FAQ
- What causes #VALUE! in Excel?
- Most often a calculation that meets text: a number with a unit typed after it, a cell with a space in it, or a date stored as text.
- Why does + give #VALUE! but SUM doesn't?
- Arithmetic operators need every cell to be a number and stop at text. SUM skips text in a range, so it returns a total without the text cell and without a warning.
- How do I find a cell that looks empty but isn't?
- Use =ISTEXT(B7) or =LEN(B7): a cell with a space returns TRUE and 1. Select it and press Delete.
- Should I use IFERROR to hide #VALUE!?
- Not before you know the cause. The error usually points at bad data; hiding it leaves that value out of your totals.



































































