Dashboards read cleaner when $1,200,000 shows as $1.2M. A custom number format scales and abbreviates large numbers — without changing the underlying value — so KPI tiles stay compact.
The example
1,200,000 shows as 1.2M.
| A | B | |
|---|---|---|
| 1 | Value | Shows |
| 2 | 1,200,000 | 1.2M |
| 3 | 45,000 | 45K |
The formula
The formula:
How it works
How it works:
- A trailing comma in a number format scales the display by 1,000 (
#,##0,"K"→ thousands). - Two commas scale by a million (
#,##0.0,,"M"). - Use a conditional format with
[<1000000]to show K below a million and M above it. - This changes only the display — the cell still holds the full number for math.
Format, not formula. Because it’s a number format, the cell keeps its true value — sums, charts, and comparisons all use the real number while the dashboard shows the tidy abbreviation. Apply it via Format Cells → Custom. Add a currency symbol ($#,##0.0,,"M") for money tiles.
Try it: interactive demo
Enter a large number.
Variations
Millions with $
Money tile:
Billions
Three commas:
Three-tier (K/M/B)
By size:
Pitfalls & errors
It’s a format, not text. The value stays numeric — don’t replace it with a TEXT string you can’t sum.
Comma counts. One comma = ÷1,000, two = ÷1,000,000, three = ÷1,000,000,000.
Conditional sections. Square-bracket conditions switch the format by value size.
Practice workbook
Frequently asked questions
How do I show big numbers as K or M in Excel?
Does this change the cell's value?
How do I switch between K and M automatically?
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