When we have large numbers, we often want to use the contraction “K” or “M” for thousands or millions respectively. To do this in Excel, we need to change the number format to the following.

For thousands (and 2 decimal places):
0.00,”K”
[1,234 is shown as 1.23K]

For millions (and 2 decimal places):
0.00,,”M”
[1,234,567 is shown as 1.23M]

One good use of number formats is in charts, as seen below. This uses the format 0,,”M” in the vertical axis and the format 0.00,,”M” in the data labels.

The above is based on a very good reference on number formats from Peltier Tech (http://peltiertech.com/Excel/NumberFormats.html) which has detailed explanations and as many things as you can think of to do with number formats!

The site also provides a conditional number format – where numbers greater than 1 million are shown with an “M”, and numbers greater than 1 thousand are shown with a “K”.

[>=1000000]0,,”M”;[>=1000]0,”K”;0