The custom format to show number in thousand or in million is simple:
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.
Nevertheless, do you know how it works?
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.
When we see, we remember. 🙂
Have you ever wondered why it is so “difficult” for such a common request? Would it be great if we could have an option like this?
Let’s request this by your VOTE in Excel User Voice.