How to format numbers in thousands, million or billions in Excel?
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!
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:
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"|
4. Then click OK to close the dialog box, and your numbers have been formatted in thousands, millions or billions as following screenshot shown:
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.
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.|
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.
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:
3. Then click OK button, and all the numbers have been formatted as the desired format based on the numbers, see screenshot:
Excel Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 1 days agoMy 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.· 1 years agoI 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 (email@example.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.· 1 years agoPerfect solution - thank you!!
To post as a guest, your comment is unpublished.· 2 years agoHi 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.· 2 years agoIts working, Do this for thousands
for millions write this in type 0,, "M"
best of luck
To post as a guest, your comment is unpublished.