Format Numbers to Millions & Thousands in Excel

Excel Number Formatting – Thousands and Millions

Excel number formatting is a larger topic than we think; we have already published topics Excel Custom Number FormattingExcel Custom Number FormattingIn Excel, Custom Number Format refers to changing the format of the numeric values as per your preferences to make the data look more visually appealing. This includes formatting large numbers in thousands or millions, formatting dates to only display the month names etc. read more, which includes all kinds of number formatting in excel. In today’s article, we will specifically concentrate on million formats of numbers in excel to allow them to show in a shorter format to read and understand very easily.

In the mathematics field, every number has different terminologies; for example, one thousand (1000) is represented as 1k, one lakh (1, 00,000) is represented as 100k. When someone says 500k, don’t get confused with it because 500k means 500,000.

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Format Numbers to Millions & Thousands in Excel (wallstreetmojo.com)

Similarly, in excel as well, we can format the numbers to show as thousands, K’s, Millions, and Billions. In this article, I will show you the techniques of modifying or changing the format of the numbers in excel.

You can download this Million Format Excel Template here – Million Format Excel Template

#1 – Excel Format Numbers in Thousands & in K’s

Firstly we will see how to format numbers in thousands & in K’s. Remember, both thousand & K are the same but need different formatting techniques in excel.

Assume you have below set of numbers in your excel sheet.

ValueFormat CodeResult
2,500??
25,000??
250,000??
70,000??
825,000??

Now we need to format these numbers in thousands; for example, I don’t want to see the number 2500, but instead, I want the formatting as 2.5 Thousand.

In order to change the view of the numbers, we need to change the formatting of the numbers.

Follow the below steps to change the formatting of the numbers.

  1. Right-click on the numbers you want to format and select Format Cells.


    Million Format Example 1-1

    Note: You can also press the shortcut key Ctrl + 1 to open the Format Cells.

  2. Now go to Custom option.


    Million Format Example 1-2

  3. In the Type: section, we need to apply the formatting code. Below is the formatting code to change the formatting of the numbers.

    Format Code: 0, “Thousands”

    Million Format Example 1-3

  4. Now, we must see values in Thousands.


    Million Format Example 1-4

    One of the problems here is 2500 is showing as 3 Thousand, but we need the exact value to be viewed here. In order to make sure decimal values are not rounded up to the nearest thousand, we need to change our formatting code as below.

    Format Code: 0.00, “Thousands”

    Million Format Example 1-5

    Now we will see exact values with decimal points.

    Million Format Example 1-6

#2 – Unconventional Way of Showing Thousand Values

Step #1 – This is the unconventional method to show a thousand values. The thing we need to do here is, we need to divide the number by 1000 and combine the word “Thousand” by using the ampersand (&) symbol.

Thousand Value Example 1-7

Step #2 – Format Numbers in K’s Values

In order to show a thousand numbers in K’s, we need to just change the word Thousand to K.

Format Code: 0.00, “K”

Thousand Value Example 1-8

Step #3 – Result is as follows:

Thousand Value Example 1-9

#3 – Format Numbers in Millions

In the previous step, we have seen how to format numbers in thousands; now, we will see how to format numbers in Millions.

Step #1 – Previous formatting code would show 10 lakhs as 1000 K, 25 lakhs as 2500 k, and so on.

Million Example 10

We all know 10 lakh is equal to 1 million. So we need to format the number in millionsMillionsThe symbol MM is used to represent numbers in millions, with the symbol M standing for thousand in roman numerals. As a result, MM stands for a thousand multiplied by a thousand, which equals one million. Financial statements and other reports from large corporations often present amounts in the millions.read more instead of in thousands. Below is the code to format the number in millions.

Step #2 – Format Code: 0.00,, “Million”

Only on the difference between the previous code & this code is we have added one extra comma (,). Then we combined the word Million to it.

Millions Example 1-1

Step #3 – This code would show the result of numbers in Millions.

Format Numbers in Millions 1-2

This format code is applicable only for millions. Imagine you have numbers that are less than 10 lakhs.

For example, 2.5 lakh would be shown as 0.25 Million instead of 2500 K. This is the common problem with a single referencing format code.

However, we can modify the code to show the result based on the cell value of numbers. For example, if the value is less than 10 lakh, the result should be in K’s, and if the value is greater than or equal to 1000,000, then the result should be in Million.

Step #4 – Format Code: [>=1000000] #,##0.0,,” M”;[<1000000] #,##0.0,” K”;General

Format Numbers in Millions 1-3

Step #5 – This code will format the numbers according to the number value and show the results accordingly.

Format Numbers result 1-4

Things to Remember While Million Format in Excel

  • Instead, Million, we can just show the alphabet M as a result.
  • Instead of thousand, we can just show the alphabet K as a result. These two are alphabetical represents for Million & Thousands.
  • In order to show the negative numbers in red color, apply the below code.

[>=1000000] $#,##0.0,,”M”;[>0] $#,##0.0,”K”;[Red]General

This has been a guide to Million Format in Excel. Here we discuss how to Format Numbers to Thousands or Millions in Excel using format code along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>