What Is Format Numbers To Millions & Thousands In Excel?
Excel number formatting is a larger topic than we think. We have already published the topic Excel Custom Number Formatting, 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.
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.
Key Takeaways
- Format numbers to millions and thousands in Excel is a technique used to convert numbers into Thousands and Millions.
- This method in Excel uses codes to convert numerals into text, i.e., thousands and millions.
- Meanwhile, format numbers to millions and thousands in Excel helps users simplify data as long numerals might be difficult to read and process.
- For instance, reading 60,000 might be difficult. We can easily convert this value as 60 Thousand using formatting numbers to thousands in Excel method.
How To Do Excel Numbers Formatting – Thousands And Millions?
Let us learn how to format Excel numbers to thousands and millions with simple examples and detailed steps.
#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 excel.
Assume we have the below set of numbers in the Excel sheet.
| Value | Format Code | Result |
|---|---|---|
| 2,500 | ?? | |
| 25,000 | ?? | |
| 250,000 | ?? | |
| 70,000 | ?? | |
| 825,000 | ?? |
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.
Follow the below steps to change the formatting of the numbers.
- 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.
#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.
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 millions 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.
Important Things To Note
- 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
Frequently Asked Questions (FAQs)
1. What is the use of formatting numbers to thousands and millions in Excel?
We all know that Excel is used to manage and store huge datasets. While using Excel, if the data has long values such as millions and thousands, it will be easy to read when the values are in words than numbers.
This method, Format numbers to millions and thousands help us convert numerals into thousands and millions with ease.
2. Explain formatting numbers to thousands and millions method in excel with an example.
For example, consider the below table showing products and the number of products sold in a year.
Now, as we see, the data in column B is long and difficult to read. Using format numbers to millions and thousands in Excel, we can convert the data into terms.
The steps are:
Step 1: Select the cells we want to format and press the shortcut keys, Ctrl + 1.
Step 2: The Format Cells window opens up. Go to the Custom option and type the code – 0, “Thousands”
We can see the values in column B is formatted into Thousand in column C.
Likewise, we can use format numbers to millions and thousands in Excel.
3. What are the steps to format numbers to thousands?
We can format numbers to text in a few steps using Excel’s Format numbers to thousands and millions method.
Step 1: Select the cells we want to format.
Step 2: Press the shortcut keys, Ctrl + 1.
Step 3: The Format Cells window opens up.
Step 4: Go to the Custom option and type the code
– 0, “Thousands”
The numbers will be converted into thousands and millions.
Recommended Articles
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: –