Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to format numbers in thousands, million or billions in Excel?

Supposing, you have a list of large numbers, now, you want to format them in thousands, millions or billions to make them look neat and concise as following screenshot shown. This article, I will talk about how to format numbers in thousands, millions or billions quickly in Excel.
 

doc format numbers in thousands 3


Method 1: Format numbers in thousands / millions / billions separately with Format Cells function

 

office tab pic

Layoff season is coming, still work slowly?
-- Office Tab boosts your pace, saves 50% work time!

  •  Amazing! The operation of multiple documents is even more relaxing and convenient than single document;
  •  Compared with other web browsers, the interface of Office Tab is more powerful and aesthetic;
  •  Reduce thousands of tedious mouse clicks, say goodbye to cervical spondylosis and mouse hand;
  •  Be chosen by 90,000 elites and 300+ well-known companies!
Full feature, Free Trial 30-day          Read More             Download Now!
 

In Excel, you can create custom format with the Format Cells feature to solve this task, please do as follows:

1. Select the list of numbers which you want to format.

2. Then right click, and choose Format Cells from the context menu, see screenshot:

doc format numbers in thousands 1

3. In the Format Cells dialog box, under the Number tab, select Custom from the left pane, in the Type text box, enter the below format code as you need:

Number Format Format code
In Thousands 0, "K"
In Millions 0,,"M"
In Billions 0,,,"B"

doc format numbers in thousands 2

4. Then click OK to close the dialog box, and your numbers have been formatted in thousands, millions or billions as following screenshot shown:

doc format numbers in thousands 3

Note: If you want to format the numbers in thousands or millions with commas as following screenshot shown, please apply this format: #,##0,"K" or #,##0,,"M" into the Format Cells dialog box.

doc format numbers in thousands 4


Solve many number problems for you, reduce work stress and enjoy comfortable life!

Kutools for Excel provides 300+ advanced functions which covers 30+ number and format features, make you become an Excel expert to attract envious glances from others!

 Have Much Spare Time: Increase 70% work efficiency, leave much time for you to love and care the family and enjoy a relaxing life.
 Enjoy A Healthy Work Style: Reduce tedious and repetitive work, relieve your tired eyes and free your keyboard hands, give you a healthy body!
 Never Worry About Being Fired: Boost your work pace, enhance your work ability, make you get success from the fierce competition.
 Get Promotion, Increase Salary: Solve  80% Excel puzzles, work better and faster than others, so that stand out from the crowd.
Full feature, Free Trial 60-day!         Read more             Download Now!

Method 2: Format numbers in thousands, millions, billions based on numbers with Format Cells function

 

If you want to format the numbers in thousands, millions or billions based on the specific numbers instead of only one number format. For example, to display 1,100,000 as 1.1M and110,000 as 110.0K as following screenshot shown.

doc format numbers in thousands 5

1. Select the cells contain the numbers you want to format, and then right click Format Cells option from the context menu.

2. In the Format Cells dialog box, under the Number tab, select Custom from the left pane, in the Type text box, enter this format code: [<999950]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B", see screenshot:

doc format numbers in thousands 6

3. Then click OK button, and all the numbers have been formatted as the desired format based on the numbers, see screenshot:

doc format numbers in thousands 5


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Adam S. · 1 days ago
    My data set has numbers in tens, hundreds, thousands, millions, and billions. Using the method 2 formula reformats my numbers in tens and hundreds with abbreviation using “k.” For example, using the method 2 formula turns “135” into “0.1k” and “45” into “0.0k.” How does the method 2 formula need to be modified to keep “135” and “45?”
  • To post as a guest, your comment is unpublished.
    Seth Smith · 1 years ago
    I GOT MY PROGRAMMED BLANK ATM CARD TO WITHDRAW A MAXIMUM OF $5000 DAILY.
    I AM SO EXCITED BECAUSE I ORDERED AND PAID FOR DELIVERY, DIDN'T GET SCAMMED, AND I NOW OWN MY HOME,
    MY BUSINESS HAS GROWN BIGGER.
    GEORG BEDNORZ (georgbednorzhackers@gmail.com) IS THE NAME OF THIS USA HACKER THAT GAVE ME THE CARD
    AND ALSO HE IS OFFERING ALL KINDS OF LOAN WITH ONLY 2% INTEREST RATE.
  • To post as a guest, your comment is unpublished.
    Darlene M · 1 years ago
    Perfect solution - thank you!!
  • To post as a guest, your comment is unpublished.
    Steven Mardas · 2 years ago
    Hi everyone

    I found this did not work for me. Try this instead:

    To display amounts in Thousands > Highlight cell(s) > Go to "Format Cells" > Go to "Custom" > Type exactly #,###,
    To display amounts in Millions > Highlight cell(s) > Go to "Format Cells" > Go to "Custom" > Type exactly #,###,###,
    and so on .....

    I found it worked for me ! Cheers !
    • To post as a guest, your comment is unpublished.
      kapil · 2 years ago
      Its working, Do this for thousands
      #,##0, "K"
      or
      0, "K"

      for millions write this in type 0,, "M"

      best of luck
      • To post as a guest, your comment is unpublished.
        Rene · 1 years ago
        It also depends on your locale. For me it only work, if I replace the commas with points. So
        0. "K"
        or
        0.. "M"
        work good on my PC with German locale.