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

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.

To explain the methods, we will use a dataset that contains information about the Annual Sales Amount of a company for six years.

## 1. Using 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

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

Note:

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.

## 2. Setting 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 the number format in millions with the comma. Here, we will use the same dataset.

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.

## 3. Inserting 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.

## 4. Using Excel TEXT Function to Apply Number Format in Millions with Comma

Another way to apply the Number Format in Millions with Commas is to use the TEXT function in Excel. The TEXT function converts a value into a text. Now, follow the steps below to learn 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.

## 5. Using â€˜Paste Specialâ€™ in Excel to Apply Number Format in Millions with Comma

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.

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.

## 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

<< Go Back toÂ Custom Number FormatÂ |Â Number FormatÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF