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.
- Simply select the number cell, or a range of numbers that you would like to convert into K or M.
- Right Click, and choose Custom Formatting.
You can also choose Number Formatting from the Home Ribbon, or simply press the shortcut [Ctrl] + 1. - 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 - Voila! Now your large number will be displayed in Thousands. So 23000 becomes 23K.
Custom Number Format Popup in Microsoft ExcelHow To Display Numbers in Millions in Excel
- Right-Click any number you want to convert. Go to Format Cells. In the pop-up window, move to Custom formatting.
- 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.
- If you would like to see the decimal point for the millions figure, like 23.6M, you can format it to 0.0,,”M”
- 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 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.
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!
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
- FREE COURSE ON PIVOT TABLES TO ANALYZE DATA – No Signup is required. Click & Start watching Videos For Free and improve your Pivot Table Skills.
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.
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!
FREE COURSE ON PIVOT TABLES TO ANALYZE DATA – Click & Start watching Videos For Free and improve your Pivot Table Skills. No signup is required.
