Confident Cells

Excel formula not calculating: showing as text, or adding to 0

20 November 2026 · 2 min read

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.

ABC
1InvoiceAmount (exported)Amount
2INV-01125.50£125.50
3INV-0280.00£80.00
4INV-0342.25£42.25
5Total0£247.75
Column B holds text, so its SUM is 0. Column C turns each into a number with =VALUE(B2), and its total is £247.75.

Fixes, from quickest to most flexible:

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

Your turn

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.

ABC
1InvoiceAmount (exported)Amount
2INV-11125.50
3INV-1280.00
4INV-1342.25
5INV-14310.00
6INV-159.99