show Number in K or Millions

For my small Training business, I haven’t reached the revenue target of Million Dollar Sales… But yes, I do achieve sales of several thousand from my blogs each month (PMChamp, ExcelChamp, PhotographyChamp) and my Training business in Singapore & Excel Dashboard Consulting around the world.

However, my clients are usually SME and MNCs, whose revenue us usually in Millions and sometimes in Billions too 🙂

Difficult to Read, Large Numbers
The problem is that for some people, it becomes difficult to read numbers and figures in Thousands, Millions and Billions, with so many zeroes to count.

And for our neighboring country Indonesia, the currency denomination is so small, that a rent of a one room apartment in Jakarta may be anywhere from 5 million to 10 million Rupiah. So you can easily imagine how doing a simple budgeting exercise can take you into dizzying heights of billions and trillions.

Adding a Thousand or Million Suffix to Numbers in Excel
For huge numbers, it is easier to read $23M or $25K rather than  $23,000,000  or $23,000. So let’s see how you can convert a large number in Thousands, Millions or Billions to a easy to read number.

1. Simply select the number cell, or a range of numbers that you would like to simplify.
2. Right Click, and choose Custom Formatting.
   You can also choose Number Formatting from the Home Ribbon, or simply press the shortcut  [Ctrl] + 1.
3. Go to Custom, and key in 0, “K” in the place where it says General, and close the popup.Custom Number Format Popup in Microsoft Excel
4.  Voila! Now your large number will be displayed in Thousands.
5.  And if you want to show the numbers in Millions, simply change the format to 0,,”M”.  The figures will now be 23M.
6. If you would like to see 23.6M, you can format it to 0.0,,”M”
7. What’s great is that if you now create a chart on this data, the chart or Pivot Table will now show the figure in this custom format. Thus you will see the chart showing in Millions, or Thousands… saving space, and making the chart or pivot easier to read and analyze.

That’s it! Spend more time analyzing, not staring at the numbers 😉

You can check this out for yourself, by downloading the Sample Excel File.

Download: Number_formatting_in_K_or_M.xlsx

This technique applies to Excel 2003, Excel 2007, Excel 2010 & Excel 2013. This would work in all versions of Microsoft Excel.

Hope it helps!

Cheers
Vinai Prakash