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"


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,” 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”


=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”


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.


format numbers in thousands millions15

format numbers in thousands millions16

Related Posts

Match Single Criteria in Excel

No repeat statistic based on a single condition is used wildly in daily work, for example, count type of products, the duplicate types are not encountered. This article describes the way of no repeats statistics for data in a single ...

Excel Array Construction

This article will talk about how to create one-dimensional array or two-dimensional array by using some functions in Excel. When using array formulas in Excel, we often use functions to construct arrays. Generate Array with ROW or COLUMN Functions Array ...

Excel Array Operation

We have all heard of arrays. Depending on the dimensionality, they are divided into one-dimensional arrays and two-dimensional arrays. Depending on the data type, they can also be divided into numeric arrays, logical arrays, and so on. The concatenation and ...

Excel Array Matrix Operation

In Excel, we have a built-in function used to perform matrix operations. It is the MMULT function. This function accepts two arrays as parameters and returns the product of the two arrays. However, this function has a prerequisite that the ...

A1 Reference Style and R1C2 Reference in Excel

In Excel, there are two reference styles. A1 reference style and R1C2 reference style. These two reference styles can help us find the cell by the cell address. Some functions return these two addresses through the argument settings. A1 Reference ...