Formatting changes display, not value
A cell showing 1,251 may hold 1250.7499. Formatting rounds the display; the underlying value is unchanged and calculations use the full precision.
That is usually what you want, and it is also why two columns that look identical can produce a total that seems wrong by a penny. ROUND() changes the value; formatting does not.
Custom formats
Ctrl+1 → Custom. A format code has up to four sections separated by semicolons:
positive;negative;zero;text| Code | Displays 1250000 as |
|---|---|
#,##0 | 1,250,000 |
#,##0,"K" | 1,250K |
#,##0,,"M" | 1M |
$#,##0.0,,"M" | $1.3M |
0.0% | (on 0.234) 23.4% |
[Red]-#,##0;[Green]#,##0 | colour by sign |
Each comma before the quotes divides by a thousand. On a summary sheet that is the difference between scannable and unreadable.
Dates are numbers
Excel stores dates as serial numbers — 1 January 1900 is 1, and 45000 is a date in 2023. A date is simply a number with a date format applied.
That is why a date sometimes appears as 45000: the format was lost. And why date arithmetic works — subtracting two dates gives days because you are subtracting numbers.
Text that looks numeric
A green triangle in the corner, left-alignment by default, and SUM returning 0 while the cells clearly hold numbers.
Fix with Data → Text to Columns → Finish, multiplying by 1 in a helper column, or VALUE(). It is the commonest reason an imported file will not total.