Vinai Prakash

Vinai Prakash is the founder and master trainer of ExcelChamp.Net. Vinai has over 30 years of experience in using Excel, and is a Data Warehousing & Business Intelligence Professional. Vinai has coached and trained over 30,000 professionals in using Excel, Power BI, Python, SQL, and regularly runs Excel Data Analysis, Excel Dashboard MasterClass, Excel for Beginners, Advanced Excel MasterClass online and all over the world. Vinai's Videos on YouTube channel and his blog articles and newsletters are read by thousands of subscribers all over the world. Vinai lives in Singapore with his wife and 2 kids.

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

by

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.

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

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

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.

Solving Everyday Problems in Excel [Survey]

by

Each week I receive questions from people facing issues with Excel. Some need help on Excel Formulas, Pivot Tables or Macros.

What issues are you facing in Excel?
[wwm_survey id=”0″]

Thanks. This will help me to create more useful Excel Tips and Tricks, that will address the issues you face with Excel.

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

Extracting Text From Variable Length Strings

by

After I wrote about how to extract characters from the left, right or middle of a text string in Excel, I received a few inquiries about extracting text from strings which don’t seem to have a fixed size, and vary in length.

For example, if the data is something like this

HQ-1022-PORT
LONDON-4053-LANDED
HOUSTON-2488-WEST
SINGAPORE-3133-LEEDON

You want to separate out the first word (HQ, LONDON, HOUSTON, SINGAPORE etc.)

Can’t Use LEFT Function
You can’t use a LEFT function, because a left function needs to know the number of characters you want to extract. And this itself is variable… There are 2 characters in HQ, 6 in London, 7 in Houston etc.

One of the ways I have discovered is to find the location of the first Hyphen (-), and extract all characters from the left of it.

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!


Finding the location of the First Hyphen
We can use the FIND function to find the location of the first hyphen.

If A2 contains HQ-1022-PORT, we can use the formula as:

=FIND(“-“,A2)

The answer would be 3. This means that the hyphen is the third character in the string. This is perfect. Now we know that we need n-1, that means 3-1, which is 2. We need 2 characters from the Left of this number.

So we can write another formula as

=LEFT(A2,  FIND(“-“,A2)-1 )

The resulting answer would be HQ. Copy the formula down to other cells, and you should be able to extract HOUSTON, SINGAPORE etc. without much hassle, and without having to write another, different formula.

One size fits all formula to extract a variable length string from a piece of text. And pretty easily too!

Do you have any other method? How would you solve this challenge?

Please post your answers in the comments below.

Cheers,
Vinai Prakash

P.S. – I am teaching a 2 day course on Data Interpretation & Analysis in Singapore on Feb 9-10, 2015, and another on March 9-10, 2015. If you would like to learn techniques to analyze data and create management reports, you can attend this program.

Awaiting you and your innovative comments below … 🙂 – Vinai

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!

New, Free, Excel Pivot Table Training For You

by

Want to learn more about Excel Pivot Tables to Analyze Business Data?

I am in the process of completing a short, free online training on Pivot Tables (8 Lessons are already available online). The course is being published by Udemy, an online training portal.

This course is called Mastering Pivot Tables to Analyze Business Data in Excel 2010 / 2013.

I have recorded 8 lessons so far. They are:

  1. What is a Pivot table?
  2. Formatting a Pivot table
  3. Customizing a Pivot table to Get it Ready for Data Analysis
  4. Adding Sub Totals in a Pivot Table
  5. Sorting Pivot Table data in multiple ways
  6. Filtering Pivot data in Unique ways
  7. Creating New Calculations in Pivot Tables
  8. Analyze data with Percentages in Excel.

Do check it out, and give me some feedback about this business data analysis with Excel training .

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!


I need specific help with these 2 questions:

  1. What other topics would you like to be covered in this Pivot table mastery training.
  2. What can be improved in this Pivot Table training video?
  3. How would you rate this training, on a scale of 1-5 (1 is lowest, 5 is highest)

If you have any other feedback, simply post it at the bottom of this post. I will surely appreciate it.

And thanks to all my supporters, friends, and family, who have given me the strength, feedback, and waited patiently while I recorded these Excel training video sessions.

Cheers & All the Best – 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!

Quick Tip: Re-Sorting a List or Table in Excel

by

Of course you know how to sort data in Excel! Simply click on the Sort button, or click on the mini A-Z or Z-A chicklet buttons, and voila! the list is sorted. And if you want more control, you can try Custom Sorting, and Advanced Sorting with this Method.

The problem is that a sorted list often gets out of sync, if you keep adding or editing data in the table. And if you use a Custom Sorting sequence, you can’t simply click on the single column sorting chicklets any longer  🙁

So you have to go to Custom Sort, and click it again to get the list sorted one more time.

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!


A Quick Way to Sort Your Custom Sorted Table in Excel
Fortunately, there is a quicker and shorter way. All you need to do is to click on the Reapply button, which shows up once you click on the Sort menu on the Home Tab.

Reapply Sorting in Excel
Reapply Sorting in Excel

There’s a shortcut – Ctrl + Alt + L

This makes the current list or table sorted again. Even if you had multiple columns in the custom sorted list.

What I have done is to put this Reapply button on my Quick Access Toolbar in Excel. This makes it much snappier to apply it anytime, anywhere in Excel.

Reapply Sorting Button in the Quick Access Toolbar
Reapply Sorting Button in the Quick Access Toolbar

Hope you like this quick tip. It has saved me quite a few clicks, and a lot of time searching for something in a not so sorted table…

Cheers,
Vinai Prakash

PS: Check out how to sort in a unique manner – not ascending , not descending. Sorting it Your Way!

Also, let me know if there is anything in Excel that bothers you…. anything where you could get some help. Simply post it into a comment or email it to me, and I’ll see what I can do to help you out 🙂 – Vinai

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!