Excel Number Formatting – Thousands and Millions

Excel number formatting is a larger topic than we think. We have already published the topic Excel Custom Number FormattingExcel Custom Number FormattingExcel custom number formatting is nothing but making the data look better or visually appealing. Excel has many inbuilt number formatting. On top of this, we can customize the Excel number formatting by changing the format of the numbers.read more, including number formatting in Excel. In today’s article, we will concentrate on million formats of numbers in Excel to allow them to be shown in a shorter form to read and understand very easily.

In the Mathematics field, every number has different terminologies. For example, one thousand (1,000) is represented as 1k, and one lakh (100,000) is described as 100k. So when someone says 500k, please don’t get confused with it because 500k means 500,000.

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

Similarly, in Excel, we can also format the numbers to show as thousands, “K,” “Millions,” and “Billions.” In this article, we will show you the techniques of modifying or changing the format of the numbers in Excel.

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

Firstly, we will see how to format numbers in thousands and K’s. Remember, both thousand and K are the same but need different formatting techniques in excelFormatting Techniques In ExcelFormatting is a useful feature in Excel that allows you to change the appearance of the data in a worksheet. Formatting can be done in a variety of ways. For example, we can use the styles and format tab on the home tab to change the font of a cell or a table.read more.

Assume we have the below set of numbers in the Excel sheet.

Now, we need to format these numbers in thousands. So, for example, we do not want to see the number 2500, but instead, we want the formatting as 2.5 Thousand.

To change the view of the numbers, we need to change the formatting of the numbers.

#2 – Unconventional Way of Showing Thousand Values

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

  • We must right-click on the numbers we want to format and select “Format Cells.” Note: We can also press the “Ctrl + 1” shortcut key to open the “Format Cells.” Now, we must go to the “Custom” option. 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” Now, we must see values in “Thousands.” One of the problems here is that 2500 shows as 3 Thousand, but we need the exact value to be viewed here. 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”Now, we can see the exact values with decimal points.

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

Format Code: 0, “Thousands”

One of the problems here is that 2500 shows as 3 Thousand, but we need the exact value to be viewed here. 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”Now, we can see the exact values with decimal points.

Step #2 –  Format numbers in K’s values.

To show a thousand numbers in K’s, we need to change the word “Thousand” to “K.”

Format Code: 0.00, “K”

Step #3 – The result is as follows:

#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 – The previous formatting code would show “10 lakhs” as “1000 K,” “25 lakhs” as “2500 K,” etc.

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 the difference between the previous code and this code is that we have added one extra comma (,). Then we combined the word “Million.”

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

This format code applies only to millions. Imagine we 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 K’s. If the value is greater than 1000,000, the result should be “Million.”

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

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

Things to Remember While Million Format in Excel

  • Instead of “Million,” we can show the alphabet M.Instead of “Thousand,” we can show the alphabet “K.” These two are alphabetical representations of “Million” and “Thousands.”To show the negative numbers in red, we must apply the below code.

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

This article is a guide to Million Format in Excel. We discuss formatting numbers to thousands or millions in Excel using format code, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Conditional Formatting in DatesConditional Formatting using FormulasVBA Format NumberUse Accounting Number Format in Excel