The guide · Numbers stored as text
How to spot them
- They line up on the left of the cell. Real numbers line up on the right.
- In Excel, a small green triangle sits in the corner, with a "Number Stored as Text" warning when you click it.
=ISTEXT(B3)returns TRUE, and=COUNT(B2:B9)counts fewer cells than you have amounts.
They usually come from somewhere else: a paste from a website, a bank or accounting export, a CSV file, or a cell formatted as Text before the number was typed.
Why SUM ignores them
SUM adds the numbers in a range and skips everything else: blanks, words, and text that happens to look like a number. The total is just lower. Plain arithmetic is different: =B2+B3 converts "1250" to 1250 on the way. That's why a long =B2+B3+… can disagree with SUM on the same cells.
Worked example
Eight payments, three of them pasted in as text: 1250, 85 and 530. =SUM(B2:B9) returns 1,835. The real total is 3,700, so SUM is 1,865 short: exactly the three text amounts.
Fix it in the formula
| Formula | Works in |
|---|---|
=SUMPRODUCT(B2:B9*1) | every version of Excel |
=SUM(VALUE(B2:B9)) | Excel 365 and 2021 (older versions need Ctrl+Shift+Enter) |
=SUM(--B2:B9) | Excel 365 and 2021 |
Each one turns every cell into a number before adding. Multiplying by 1, VALUE, and the double minus all turn "1250" into 1250. Because the fix lives in the formula, the next import with the same problem is handled too.
Or fix the data
For a one-off clean-up, select the cells and choose Convert to Number from the warning, or use Data → Text to Columns → Finish. A helper column with =B2*1 or =VALUE(B2) works too. These fix the data once; the next paste brings the problem back.
Lookups have the same problem
The text "1001" and the number 1001 don't match, so VLOOKUP and MATCH return #N/A even though the value looks the same. The #N/A mistake hunt covers that case.
Common mistakes
- Multiplying the total by 1. In
=SUM(B2:B9)*1, SUM has already skipped the text. Convert each cell, not the total. - SUMPRODUCT on its own.
=SUMPRODUCT(B2:B9)counts text as 0. It needs the*1inside. - Typing the missing amounts into the formula. Right until one of them changes.
- Changing the cell format to Number. The format changes how a value looks, not what's stored. Text stays text until it's converted.
FAQ
- Why is my Excel SUM not adding up?
- Some numbers are probably stored as text, and SUM skips them without a warning. Look for numbers that line up on the left, or compare =COUNT(range) with how many numbers you expect.
- How do I convert numbers stored as text to numbers?
- Inside a formula, multiply by 1, use VALUE, or put a double minus in front. In the data itself, select the cells and use Convert to Number, or Data → Text to Columns → Finish.
- How do I sum numbers stored as text in Excel?
- =SUMPRODUCT(B2:B9*1) works in every version. In Excel 365 and 2021, =SUM(VALUE(B2:B9)) works too.
- Why doesn't formatting the cells as Number fix it?
- A format changes how a value is shown, not what is stored. Text stays text until it is converted, for example with Text to Columns or by entering it again.