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.
- Format numbers in thousands, millions, billions separately with Format Cells function
- Format numbers in thousands, millions, billions based on numbers with Format Cells function
- Convert abbreviative numbers to normal long numbers (1K = 1000) with formula
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:
4. Then click OK to close the dialog box, and your numbers have been formatted in thousands, millions or billions as following screenshot shown:
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!
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:
Sometimes, you want to do the opposite operation to convert the abbreviative numbers to normal long numbers as below screenshot shown:
Please enter or copy the below formula into a blank cell where you want to get the result:
Then, drag the fill handle down to the cells you want to apply this formula, and all the abbreviative numbers have been formatted to the normal long numbers as you need, see screenshot:
More relative format clles articles:
- Format The Cell Value Red If Negative And Green If Positive In Excel
- If there are a long list of numbers in your worksheet, now, you want to format the negative values as red font and positive numbers ad green font for viewing them easier. In Excel, there are some methods for dealing with this job quickly and easily.
- Format Number As Ordinal (1st 2nd 3rd ) In Excel
- In some cases, we need to format number as 1st, 2nd, 3rd, etc. when we rank as below screenshot shown. However, do you know how to format these cardinal numbers as ordinal numbers in Excel? In general, there is no built-in function to convert number as 1st in Excel, but, here I can introduce some tricks on this problem.
- Apply Data Validation To Force Phone Number Format In Excel
- May be, there are various phone number formats can be used when typing into the Excel workbook. But, how could you only allow one phone number format to be entered in a column of worksheet? For example, I just want the phone number as this format 123-456-7890 to be allowed entering in a worksheet.
- Validate Email Addresses In A Column Of Worksheet
- As we all known, a valid email address is composed of three parts, the user name, the "at symbol" (@), and the domain. Sometimes, you just allow others to enter only the email address format text into a specific column of worksheet. Is it possible to achieve this task in Excel?
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.· 6 months 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.· 2 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.