The custom format to show number in thousand or in million is simple:
- #,##0,“k”
- #,##0,,“M”
The “k” or “M” is optional, depend on whether you want to show it in the header or in the figure itself.
The trick is to put 1 comma and 2 comma at the end of the format setting (#,##0) for “thousand” and “million” respectively.
Show number in thousand (k) or in million (M) by using custom format
Think about a number like 1000000. When you apply custom format #,##0 to it, it shows 1,000,000. The comma (,) we use in the custom format is simply the thousand separator.
When we put one more comma at the end without specifying the format (# or 0) after it, it means “Show Nothing”. Put it in other words, we cross out the final three digits with a comma.
- Applying #,##0,“k” to 1000000 –> 1,000,
000k –> 1,000k - Applying #,##0,,“M” to 1000000 –> 1,
000,000M –> 1M
As mentioned, “k” or “M” is optional.