How to abbreviate large numbers as K and M
✓ Verified in LibreOffice 25.8.7.3Show 3,400,000 as "3.4M" — dashboards, charts, tight columns.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =TEXT(A2,"0.0,,""M""") | Each trailing comma divides the display by 1,000. As a cell format: 0.0,,"M" keeps the value numeric. |
| Google Sheets | =TEXT(A2,"0.0,,""M""") | Identical, including the custom-format route. |
| LibreOffice Calc | =TEXT(A2,"0.0,,""M""") | Identical. |
How it works
A comma at the END of a number format's digit section scales the display down by a thousand — two commas mean millions, so 3,400,000 renders as "3.4M". One comma with "K" does thousands. Applied as a custom cell format instead of TEXT, the cell still holds the real number for math while displaying short. For mixed magnitudes on one sheet (930K next to 1.2M), you need a conditional: =IF(A2>=1E6,TEXT(A2,"0.0,,""M"""),TEXT(A2,"0,""K""")).
Verified, not just documented
We ran =TEXT(A2,"0.0,,""M""") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 3.4M — exactly the expected result. Every formula here is confirmed by actually executing it.