Show number in thousand (k) or in million (M) by using custom format

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.

Excel Tips - Custom Format 1

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. 🙂

This entry was posted in Excel Basic, Excel Tips and tagged . Bookmark the permalink.

3 Responses to Show number in thousand (k) or in million (M) by using custom format

  1. Matthew Kuo says:

    Great tip on custom number formatting. I often feel that this feature is one of the most underutilized in Excel. Below is my article on other custom number formatting tricks. Look forward to reading more from you.

    http://www.mbaexcel.com/excel/how-to-use-excel-custom-number-formatting/

    Like

  2. Pingback: Sum a range of number end with “k” | wmfexcel

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s