Mursalin Ibne Salehin
In this article, we will learn to apply the number format in millions with the comma in Excel. Often, we need to use large numbers in our excel sheet. It becomes difficult to read those numbers if the formatting is not applied accurately. In this type of case, it is better to represent numbers in millions. So, today, we will discuss five easy techniques to apply the number format in millions with the comma in excel.
Download Practice Book
Download the practice book here.
5 Ways to Apply Number Format in Millions with Comma in Excel
To explain the methods, we will use a dataset that contains information about the Annual Sales Amount of a company for six years.
1. Use Custom Format to Apply Number Format in Millions with Comma in Excel
In the first method, we will use a custom format to apply the number format in millions with commas. In the Custom section, we can use the pound (#) sign as well as the zero. Here, we will show these two ways in the following sub-methods.
1.1 Use of Placeholder Pound (#) Sign
Let’s follow the steps below to apply the number format in millions using the pound (#) sign.
STEPS:
- In the first place, select the columns that contain the numbers. We have selected Column C You can also select a single cell if you have one large number only.
- Secondly, press Ctrl + 1 to open the Format Cells window.
- Select Number and then, go to Custom.
- After that, write the format string in the Type field:
#,##0,, “M”- Click OK to proceed.
- Finally, you will see results like the below picture.
1.2 Use of Placeholder Zero and Decimal Point
We can also use zero and decimal points as placeholders. Pay attention to the steps below to know more about this sub-method.
STEPS:
- Firstly, select Column C.
- Secondly, press Ctrl + 1. It will open the Format Cells window.
- Now, select Number and then, select Custom.
- After that, put the below format string in the Type field:
0.0,, “M”- Click OK to proceed.
- After clicking OK, you will see results like the below.
- Now, if you select Cell C10, you can see it displays the value to one decimal point.
- To display the number to two decimal points, write the below format string in the Type field:
0.00,, “M”
- In the end, click OK to see results like the below screenshot.
Read More: Custom Number Format: Millions with One Decimal in Excel (6 Ways)
2. Set Number Format in Millions with Comma Using Excel Conditional Formatting
Excel allows us to apply conditional formatting using different rules. Interestingly, we can use the excel conditional formatting to set number format in millions with the comma. Here, we will use the same dataset.
Let’s follow the steps below.
STEPS:
- In the beginning, select the column that contains the numbers. We have selected Column C here.
- After that, go to the Home tab and select Conditional Formatting. A drop-down menu will occur.
- Select New Rule from the drop-down menu. It will open the New Formatting Rule window.
- In the New Formatting Rule window, select Format only cells that contain at first.
- Then, select greater than or equal to and type 1000000 in the Format only cells with field.
- Next, select Format. It will open the Format Cells window.
- Select Number and then, Custom in the Format Cells window.
- Now, type the below format string in the Type field:
#,##0,, “M”
- Click OK to proceed. Also, click OK in the New Formatting Rule window.
- Lastly, you will see results like the below picture.
Read More: Excel Custom Number Format Multiple Conditions
3. Insert Excel ROUND Function to Implement Number Format in Millions with Comma
In the third method, we will insert the ROUND Function to implement the number format in millions with commas. The ROUND Function rounds a number to a specified number of digits. Let’s observe the steps below to learn more.
STEPS:
- First of all, create a helper column like the one below.
- After that, select Cell D5 and type the formula:
=ROUND(C5/1000000,1)&" M"
- Now, hit Enter to see the result in Cell D5.
Here, the formula first divides the number of Cell C5 by 1000000 and then, rounds it up to 1 digit. We have used the ampersand (&) sign to display M which denotes a million.
- Finally, drag the Fill Handle down to see results in all cells.
Read More: How to Round Off Numbers in Excel (4 Easy Ways)
Similar Readings
- Excel round to nearest 10000 (5 Easiest Ways)
- How to Round up Decimals in Excel (4 Simple Ways)
- How to Round to Nearest 5 in Excel (3 Quick Ways)
- Excel Round to 2 Decimal Places (with Calculator)
- How to Add Leading Zeros in Excel (4 Quick Methods)
4. Use Excel TEXT Function to Apply Number Format in Millions with Comma
Another way to apply Number Format in Millions with Comma is to use the TEXT Function in excel. The TEXT Function converts a value into a text. Now, follow the steps below to know about the use of the TEXT Function to format numbers in millions.
STEPS:
- At the start, create a helper like the below picture.
- In the second place, select Cell D5 and type the formula:
=TEXT(C5,"#,##0,, ")&"M"
- Then, press Enter to see the result.
Here, the TEXT Function first converts the number of Cell C5 to a text string and formats it as the pound (#) sign. We have used the ampersand (&) sign to display M which denotes a million.
- Finally, use the Fill Handle to see results in all cells.
Read More: How to Custom Cell Format Number with Text in Excel (4 Ways)
5. Number Format in Millions with Comma Using ‘Paste Special’ in Excel
In the last method, we will use the ‘Paste Special’ option to apply the number format in millions with commas. Here, we will use the previous dataset with some changes. Here, we won’t use the Currency format. We have also changed the column header to Annual Sales Amount (Millions). It denotes that the unit of the numbers in that column is Million.
Let’s follow the steps below.
STEPS:
- To begin with, select any cell and type 1000000 in that cell. We have selected Cell E5 and typed 1000000 here.
- After that, press Ctrl + C to copy.
- Now, select the cells that contain the large numbers.
- After selecting the cells, press Ctrl + Alt + V to open the Paste Special window.
- Select Values in the Paste field and Divide in the Operation field.
- Now, click OK to proceed.
- Finally, you will see results like the image below.
Related Content: How to Change Number Format from Comma to Dot in Excel (5 Ways)
Things to Remember
Sometimes, in Method-1, you may not get the precise value after formatting a number in millions. To fix this, change the decimal points in the Type field of the Format Cells window.
Conclusion
We have demonstrated 5 easy and quick methods to apply the number format in millions with commas in excel. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.
