Excel Tip - Formatting numbers for thousands or millions

Apologies for the the lack of posts over the last few weeks. I intentionally took two weeks break while I was on holiday on the Isle of Skye with the family, then I have involuntarily missed the last two weeks while catching up since I got back!

I'm back now with a simple post that I have been asked about on numerous occasions.

"How can you show a number as millions or thousands? e.g. show, 3,000,000 as 3 million."

Well, the good news is that you can do it simply with a custom number format.

There is a full description of how number formats work available here, if you wish to know a lot more about this subject, but we don't need to understand all of that to format millions or thousands as required here.

If you right-click the cell or range that you wish to apply the number format to, and select "Custom", you will be presented with a box to allow you to create the format. This will be pre-populated with the current number format of the cell, and you can choose to start from any other.

We'll work from scratch with a simple format in this case, then you should be able to apply the same principles to more complicated formats.

Let's start with a number format of

#,##0.00

This is a commonly used format, to show numbers with two decimal places and a comma separator for the thousands.

The characters mean the following:

#  -  show this character only if required (e.g. if the number was 12, it would show as 12.00, not 0,012,00)

0  -  always show this character, even if it is not significant (this example will always display two decimal places, and a zero before the decimal place if it is less than 1)

,  -  use a thousands comma separator (this works for all thousands)

In this format, 15,450,324 would appear as 15,450,324.00, but we might want it to appear as any of the following for example:

15 million
15.45 million
15,450 thousand

We can do this by use of the comma and the insertion of text. Taking the last example, we can remove everything after the comma, swap the hash for a 0 (so that we still get a zero, if there is less than a thousand) and add the text, i.e.

0," thousand"

This, however gives us

15450 thousand

We can re-introduce the comma as a thousands separator as follows:

#,##0," thousand" giving us 15,450 thousand

If we want to do millions, we can add a second comma on the end, i.e.

#,##0,," million" giving us 15 million

We can also put decimal places before the commas, e.g.

#,##0.00,," million" giving us 15.45 million.

Have a play, and a read of the Office Support article on the subject, if you want to see what else you can do.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".