How to Format Numbers to Thousands & Millions & Billions in Excel

This post will guide you how to format numbers into thousands or millions or billions with Format Cells function in Excel 2013/2016. How do I format large numbers with Thousands or Millions separators in Excel.

For example, number 8000 should be shown as 8 or 8K, 8000000 should be shown as 8 or 8M. and Excel does not provide such option with a single click or way. And you can use number formatting feature to achieve the result.

Excel Number Formatting is a larger feature in Excel, and we have written lots of posts which includes all kinds of number formatting in Excel. Number Formatting allows you to modify the appearance of cell values without changing their real values. Also you can also add Thousands or Millions separators without changing the cell actual values.

In Microsoft Excel, if you want to improve the readability of your data by formatting numbers to show as thousands(k), Millions(M), or Billions(B). The below steps will show you how to format numbers with Thousands or Millions separators to show in a shorter format to read and understand very easily by creating a custom number format.

Format Numbers in Thousands


Firstly, we will show you how to format numbers in thousands by create a custom format with the Format Cells function in your worksheet.

Assuming that you have a list of data with the below set of numbers in cell range A1:A5. And now we need to format these numbers in thousands.

Just do the following steps to change the formatting of the numbers:

Step1: select your numbers in range A1:A5.

format numbers in thousands millions1

Step2: right click on the selected cells that you want to format. and select Format Cells menu from the pop-up menu list. And the Format Cell dialog box will appear. Or you can also press the shortcut key CTRL +1 to open the Format Cells dialog box.

format numbers in thousands millions2

Step3: click Number tab in the Format Cells dialog box, and click the Custom option from the left pane.

format numbers in thousands millions3

Step4: now go to Type: section in the Format Cells dialog box, add the following formatting code to change the formatting of the selected numbers. Click Ok button.

0, "K"

Or

0, “ Thousands”

format numbers in thousands millions4

 

Step5: your selected numbers will appear in thousands automatically. And the formatting does not change the integrity or truncate your numeric values in any way. And it will apply a cosmetic effect to the number.

format numbers in thousands millions5

format numbers in thousands millions6

If you want to show the exact value, and you can change the formatting code as below:

0.000,”K”

Or

0.000,” Thousands”

Then you will see that the exact values with decimal points should be shown.

format numbers in thousands millions7

format numbers in thousands millions8

Format Numbers in Thousands with Formula


There is another method to format numbers in thousands separator in Excel. And you can create a formula based on concentrate character. You need to divide the number by 1000 and combine the word “Thousands” or character “K” by using concentrate character “&”. Type the following formula in a blank cell:

=A1/1000 & “K”

Or

=A1/1000 & “ Thousand”

format numbers in thousands millions9

Then you can drag the Fill Handle down to other cells to apply this formula.

format numbers in thousands millions10

Format Numbers in Millions


The above steps have shown you how to format numbers in thousands, and the below steps will show you how to format number in Millions. Just do the following steps:

Step1: open the Format Cells dialog box, and click the Custom option.

Step2: add the following format code in the Type: section. The only difference between previous code and this format code is that you need to add one extra comma(,).

0.000,, “M”

Or

0.000,, “ Million”

format numbers in thousands millions11

Step3: the result is as below:

format numbers in thousands millions12

Format Numbers in Billions


In the previous step we have talked that how to format numbers in thousands and millions. And now we will see that how to format numbers in Billions.

You just need to refer to the above steps, and then use another format code to change the formatting of the numbers in Billions.

0.000,,, “B”

format numbers in thousands millions13

format numbers in thousands millions14

Format Numbers in Thousands, Millions, Billions Based on Numbers


If you want to format numbers to show the result based on the cell values. For example, if the cell value is less than 10000, and the result display as 10K, and if the cell value is greater than or equal to 1000000, then the result is displayed in Million. You can add the following format code into Type: section.

[<999950]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B"

format numbers in thousands millions15

format numbers in thousands millions16

Related Posts

Filter And Transpose Data From Horizontal To Vertical
filter and transpose horizontal to vertical1

This post will show you how to use Filter function and in combination with Transpose function to filter data from horizontal and transpose data as vertical in Microsoft Excel. You can refer to the below general formula based on TRANSPOSE ...

VLOOKUP Formula | Faster Trick with 2 VLOOKUPS
Faster VLOOKUP with 2 VLOOKUPS1

This post will guide you how to use 2 VLOOKUPS function to looking up data entries from a given range of cells in Microsoft Excel. VLOOKUP with 2 lookups can be faster than a single VLOOKUP in certain scenarios. The ...

Extract Unique Items From A List
Extract Unique Items From A List In Excel1

This post will guide you how to extract unique itmes from a given list in Microsoft Excel. How to create a newly formula to get unique values from a range cells in Excel. The unique list of items is the ...

Extract substring In Excel
Extract substring In Excel1

This post will guide you how to use Excel's MID function is a quick and easy way to extract pieces from your text. Use the Excel formula to extract a substring with MID. Note: If you want to extract just ...

How to Use 3D SUM Multiple Worksheets
3D sum multiple worksheets1

To sum a range of numbers is straightforward for most Excel users, but do you know how to establish a 3D reference to total the same range of numerous sheets. In this post, I will present the steps for this ...

Extract all the matches with helper Column
how to extract all matches with helper column1

With Excel's powerful functions IF, INDEX, and MATCH, we can find exactly what you're looking for with a few clicks of the mouse. This step-by-step tutorial will show how easy it is to extract data using these tools and more! ...

If Cell is This Value or That Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of logical test. If you want to see if a cell is A or B, and if one of ...

If Value is Greater Than A Certain Value
If Value is Greater Than A Certain Value 1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the logical test result. If you want to see if a value in one cell is greater than a specific value, ...

If Cell is Not Blank
If Cell is Not Blank 6

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

VBA Macro For VLOOKUP From Another Sheet
vba macro for vlookup from another sheet1

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data ...

Sidebar