How to Apply Number Format in Millions with Comma in Excel (5 Ways)

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.

Use Custom Format to Apply Number Format in Millions with Comma in Excel

  • 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.

Use Custom Format to Apply Number Format in Millions with Comma in Excel

  • Finally, you will see results like the below picture.

Use Custom Format to Apply Number Format in Millions with Comma in Excel


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.

Use Custom Format to Apply Number Format in Millions with Comma in Excel

  • 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.

Use Custom Format to Apply Number Format in Millions with Comma in Excel

  • After clicking OK, you will see results like the below.

Use Custom Format to Apply Number Format in Millions with Comma in Excel

  • Now, if you select Cell C10, you can see it displays the value to one decimal point.

Use Custom Format to Apply Number Format in Millions with Comma in Excel

  • To display the number to two decimal points, write the below format string in the Type field:
0.00,, “M”

Use Custom Format to Apply Number Format in Millions with Comma in Excel

  • In the end, click OK to see results like the below screenshot.

Use Custom Format to Apply Number Format in Millions with Comma in Excel

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.

Set Number Format in Millions with Comma Using Excel Conditional Formatting

Set Number Format in Millions with Comma Using Excel Conditional Formatting

  • Select New Rule from the drop-down menu. It will open the New Formatting Rule window.

Set Number Format in Millions with Comma Using Excel Conditional Formatting

  • 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.

Set Number Format in Millions with Comma Using Excel Conditional Formatting

  • Select Number and then, Custom in the Format Cells window.
  • Now, type the below format string in the Type field:
#,##0,, “M”

Set Number Format in Millions with Comma Using Excel Conditional Formatting

  • Click OK to proceed. Also, click OK in the New Formatting Rule window.

Set Number Format in Millions with Comma Using Excel Conditional Formatting

  • Lastly, you will see results like the below picture.

Set Number Format in Millions with Comma Using Excel Conditional Formatting

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.

Insert Excel ROUND Function to Implement Number Format in Millions with Comma

  • After that, select Cell D5 and type the formula:
=ROUND(C5/1000000,1)&" M"

Insert Excel ROUND Function to Implement Number Format in Millions with Comma

  • Now, hit Enter to see the result in Cell D5.

Insert Excel ROUND Function to Implement Number Format in Millions with Comma

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.

Insert Excel ROUND Function to Implement Number Format in Millions with Comma

Read More: How to Round Off Numbers in Excel (4 Easy Ways)


Similar Readings:


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"

Use Excel TEXT Function to Apply Number Format in Millions with Comma

  • 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.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo