Big numbers crowd a dashboard. A trailing comma in the format code scales the display by thousands — show $4,200,000 as “4.2M” while the cell keeps its full value.
The example
Full values displayed compactly.
| A | B | |
|---|---|---|
| 1 | Value | Displays |
| 2 | 4,200,000 | 4.2 M |
| 3 | 85,000 | 85 K |
The formula
Trailing commas scale the display:
How it works
A comma at the end of a format means “divide by 1,000”:
- One trailing comma scales the display by 1,000 (thousands → K).
- Two trailing commas scale by 1,000,000 (millions → M).
- Add a label in quotes —
" K"," M"— and decimals (#,##0.0) for precision. - The cell still holds the true number; only the appearance changes, so charts and formulas are unaffected.
Auto-scale per value? A single format can’t switch between K and M based on size. For that, build a label with a formula: =TEXT(A1/1e6,"0.0")&"M" when ≥ 1M, else K — or use conditional number-format sections for two thresholds.
Try it: interactive demo
Pick a scale; value stays the same.
Variations
Thousands (K)
One trailing comma:
Auto label (formula)
Switch K/M by size:
Millions, no decimals
Whole millions:
Pitfalls & errors
Commas must be trailing. A comma between placeholders is a thousands separator; only commas at the end of the numeric code scale the value.
Display only. “4.2M” still holds 4,200,000 — great for readability, but tell readers the units so they don’t misread.
One scale per format. A single code can’t auto-pick K vs M; use a formula or conditional sections for that.
Practice workbook
Frequently asked questions
How do I show large numbers as millions in Excel?
How do I show thousands (K)?
Can one format auto-switch between K and M?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class