show Number in K or Millions

My clients keep asking me to display large numbers like in Thousands or Million to display in a shorter form. In the business world, K or M are used to represent Thousands or Millions.

For my own 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) and my Training company in Singapore & SkillsFuture Training Courses in Singapore.

However, my clients are usually small and medium enterprises as well as Multi-national companies, whose revenue is usually in Millions and sometimes in Billions too 🙂

Difficult to Read, Large Numbers look ugly in Excel
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 neighbouring 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 Indonesian Rupiah. So you can easily imagine how doing a simple budgeting exercise can take you into dizzying heights of billions and trillions.

How To Add 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 be an easy to read number with Microsoft Excel.

1. Simply select the number cell, or a range of numbers that you would like to convert into K or M.
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. Now close the Format Cells popup window.Custom Number Format Popup in Microsoft Excel
4.  Voila! Now your large number will be displayed in Thousands. So 23000 becomes 23K.

How To Display Numbers in Millions in Excel

1. Right-Click any number you want to convert. Go to Format Cells. In the pop-up window, move to Custom formatting.
2. If you want to show the numbers in Millions, simply change the format from General to 0,,”M”.  The figures will now be 23M. So essentially we are adding 2 commas instead of a single comma this time.
3. If you would like to see the decimal point for the millions figure, like 23.6M, you can format it to 0.0,,”M”
4. 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.
5. Thus you will see the chart displaying numbers 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 huge numbers 😉

You can check this out for yourself, by downloading the Sample Excel File. It has a few scenarios where you can try to convert large numbers into Millions or Thousands, whatever you prefer.

Sample Excel File Download: Number_formatting_in_K_or_M.xlsx

This technique applies to Excel 2003, Excel 2007, Excel 2010,  Excel 2013, Excel 2016, Excel 2019 & Microsoft Office 365 editions.

It would work in all versions of Microsoft Excel in the past 20 years…

Hope it helps! Do share it with others on Facebook or LinkedIn.

And do comment or write to tell me of any issues you are facing in using Excel every day. There may be a simple solution that can save you a lot of time. At ExcelChamp, I solve many small problems each day to make Excel easy for everyone.

Cheers
Vinai Prakash

Need Tips on Pivot Tables, Data Analysis or Creating Better Charts?

Do checkout these resources

• How To Show Values and Percentages in Excel Pivot Tables
• 7 Habits of Highly Effective Data Analysts
• Creating Beautiful Excel Charts For Business Presentations
• Master Excel Lookup Functions like VLOOKUP, HLOOKUP, INDEX, MATCH, OFFSET

And if you want to learn Advanced Excel fast, then check out our detailed guide. Plus Master Lookup Functions like VLOOKUP, HLOOKUP, INDEX in Excel.