Here is a fact that quietly explains half the “Excel is lying to me” moments you’ll ever have: what a cell shows and what a cell holds are two different things. The cell holds a value. The number format dresses it for display. Change the format and the costume changes — the value underneath never does.
Type 0.1 into a cell, then dress it four ways:
| Format | The cell shows | The cell holds |
|---|---|---|
| General | 0.1 | 0.1 |
| Percentage | 10% | 0.1 |
| Currency | £0.10 | 0.1 |
| Time | 02:24 | 0.1 |
Same value, four outfits. Every formula pointing at that cell sees
0.1 regardless — formulas read values, never costumes. Once this
clicks, a whole family of mysteries dissolves.
The mysteries, dissolved
“Excel changed my number!” — No: it re-dressed it. Type
07/03 and Excel decides you meant a date, stores a date, and
formats accordingly. (Dates, remember,
are just day-numbers in costume
— format a date as General and the serial number looks back at
you. Nothing is broken; you’re seeing backstage.)
“The total is 1p off.” — Display rounding. The cells show
two decimals but hold full precision: £10.005 and £10.005
display as £10.01 and £10.01, but sum to £20.01, not £20.02. The
costume rounds; the values don’t. (What to do about it — ROUND
and friends — is a stage-three lesson of its own.)
“It shows #### now.” — The column is too narrow for the costume, nothing more. Widen it, or double-click the column edge.
“It’s a number but it won’t sum.” — The reverse disease: a text value wearing a number’s costume. No format will fix text; that’s a cleaning job. Format changes how values dress — it cannot change what they are.
Driving the wardrobe
Everything lives in Ctrl+1 → Number (one of
the twelve keys).
The working set: Number with a thousands separator for general
figures; Currency for money; Percentage — noting that typing
10% directly stores 0.1, which is exactly what your formulas
want to multiply by; Date, where the same underlying day-number
can display as 14/07/2026 or Jul-26 for free; and Text, the
one to avoid for anything you’ll calculate with.
Two costume-room warnings. Typing a £ sign yourself sometimes
creates text in older versions — let the format add symbols, you
just type digits. And “format the cell as Number” cannot cure a
text-number — the costume changes nothing underneath; only
re-evaluation
does.
The 5% that’s custom
Behind the presets sits a small pattern language (Ctrl+1 →
Custom) — #,##0;[Red](#,##0);"—" gives you black positives, red
bracketed negatives, a dash for zero, all without touching a
value. Accountants live here. You need perhaps two recipes:
that one, and #,##0, with a trailing comma, which displays in
thousands while holding the full number — the honest way to
present big figures (never divide your actual data by 1,000 for
cosmetics; you’d be editing values to change a costume, the exact
sin in reverse).
The deeper lesson is the grid mindset applied to appearance: values are the truth; display is a courtesy to the reader. Keep the two separate in your head and you’ll format fearlessly — because you’ll know, always, that the costume can’t touch the numbers.