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

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

EDIT:

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.

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Basic, Excel Tips and tagged . Bookmark the permalink.

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

1. Mantake says:

In Excel & Excel VBA, I want to be able to display 1 000 000 000, 1 00 000 and 1 000 as 1b, 1m and 1k respectively with thousands separator. I want to be able to use this for both positive and negative numbers, with negative numbers showing in red.

I also want to show 3.1k and but 3.0k as 3k

I am using this custom format, Which works for millions and thousands but not for negative numbers [>=1000000]R0 \m;[>=1000]R0 k;R0

Like

2. Gnanaguru says:

Does anyone changed the CUrrency display format Applying #,##0,“k” to 1000000 –> 1,000,000k –> 1,000k –> 1000 M
Applying #,##0,,“M” to 1000000 –> 1,000,000M –> 1M –> 1 MM
For Billion MMM ?

Like

3. 03104730018 says:

M Khalid Pathan

Like

4. Anne Broxton says:

That works a treat for me. I have many different users accessing and editing a spreadsheet on SharePoint and I wish them to input as £20K, £100K (instead of 20000, or £20,000 etc) for the sake of consistency. The format I have used is £#,##0,”K”
This happily converts all entries as per the example above to £20K, or £100K etc. However, If someone were to input £20,800, it still appears as £20K. Is there any way of making it £20.8K without adding .0 to the other entries? Hope this makes sense.

Liked by 1 person

• MF says:

Try £#,##0.0, “K”

Like

• Anne Broxton says:

Thank you very much, MF. This gives me £28.8 K (if 28800 is input) and £28.0 K if 28000 is input. I was rather hoping that if there was no decimal after the thousand, ie £28 K, this would end up as £28 K, and not £28.0 K, but I guess that is not possible.
Many thanks again for your help.

Like

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

• MF says:

Thanks Matthew for sharing! 😀

Like

• Smitha says:

Thank you so much Matthew, this is really helpful. it answered my question. 🙂
Regards,
Smitha.

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.