Excel formula not calculating: showing as text, or adding to 0
If a cell shows =SUM(B2:B5) instead of the answer, the cell was formatted as Text before you typed, or Show Formulas is on. If SUM gives 0, or less than it should, some of the numbers are really text. Each has a quick fix.
The formula shows instead of the answer
The cell is formatted as Text
Excel stores whatever you type in a Text cell as text, even a formula. Change the format to General (Home, Number format), then click into the cell and press Enter to re-enter it.
Show Formulas is on
If every formula on the sheet shows, someone pressed Ctrl+` (the key above Tab). Press it again, or go to Formulas, Show Formulas.
A space or apostrophe before the =
The formula must start with = as the very first character. =A2*2 with a space, or '=A2*2, is just text.
The answer doesn't update
You change a number and the total stays the same. Calculation has been switched to Manual, often by a large workbook someone else opened. Go to Formulas, Calculation Options, Automatic. In the meantime, F9 recalculates.
SUM gives 0: numbers stored as text
Amounts exported from another system can arrive as text that looks like numbers. The clues: they sit on the left of the cell, there's a small green triangle in the corner, and SUM skips them.
| A | B | C | |
|---|---|---|---|
| 1 | Invoice | Amount (exported) | Amount |
| 2 | INV-01 | 125.50 | £125.50 |
| 3 | INV-02 | 80.00 | £80.00 |
| 4 | INV-03 | 42.25 | £42.25 |
| 5 | Total | 0 | £247.75 |
Fixes, from quickest to most flexible:
- Select the cells, click the warning sign that appears, and choose Convert to Number.
- Use
=VALUE(B2)in a new column and copy it down. If there are spaces too,=VALUE(TRIM(B2)). - Multiply by 1:
=B2*1does the same job.
Dates stored as text cause the same problems, and the numbers and dates stored as text lesson covers turning those back into real dates.
Try it now
The amounts in column B came from a finance system as text, so SUM ignores them. In C2, turn each one into a real number. Your formula will be copied down to C6.
| A | B | C | |
|---|---|---|---|
| 1 | Invoice | Amount (exported) | Amount |
| 2 | INV-11 | 125.50 | |
| 3 | INV-12 | 80.00 | |
| 4 | INV-13 | 42.25 | |
| 5 | INV-14 | 310.00 | |
| 6 | INV-15 | 9.99 |