Displaying Large Numbers in K (thousands) or M (millions) in Excel

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.

How to Show Large numbers in K or M within Excel
Large Numbers showing in K or M in Excel

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.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


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 ExcelCustom 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

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!