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

## Method 1 – Using Custom Format to Apply Number Format in Millions with Comma in Excel

### 1.1 Use of Placeholder Pound (#) Sign

STEPS:

• Select the columns that contain the numbers. We have selected Column C. You can also select a single cell if you have only one large number.

• Press Ctrl + 1 to open the Format Cells window.
• Select Number and go to Custom.
• Enter the following formula in the Type field.
`#,##0,, "M"`
• Click OK to proceed.

### 1.2 Use of Placeholder Zero and Decimal Point

STEPS:

• Select Column C.

• Press Ctrl + 1. It will open the Format Cells window.
• Select Select Custom.
• Enter the following formula in the Type field:
`0.0,, "M"`
• Click OK to proceed.

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

• To display the number to two decimal points, enter the following formula in the Type field:
`0.00,, "M"`

• Click OK.

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.

## Method 2 – Setting Number Format in Millions with Comma Using Excel Conditional Formatting

STEPS:

• Select the column that contains the numbers. We have selected Column C.

• Go to the Home tab and select Conditional Formatting.

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

• Select Format only cells that contain.
• Select greater than or equal to and enter 1000000 in the Format only cells with field.
• Select Format. It will open the Format Cells window.

• In the Format Cells window, click Number tab and select Custom.
• Enter the following formula in the Type field:
`#,##0,, "M"`

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

## Method 3 – Inserting Excel ROUND Function to Implement Number Format in Millions with Comma

STEPS:

• Create a helper column.

• Select Cell D5 and add the formula:
`=ROUND(C5/1000000,1)&" M"`

• Press Enter to see the result in Cell D5.

The formula first divides the number of Cell C5 by 1000000 and rounds it up to 1 digit. We have used the ampersand (&) sign to display M which denotes a million.

• Drag the Fill Handle down to see results in all cells.

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

STEPS:

• Create a helper.

• Select Cell D5 and enter the formula:
`=TEXT(C5,"#,##0,, ")&"M"`

• Press Enter to see the result.

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.

• Use the Fill Handle to see results in all cells.

## Method 5 – Using ‘Paste Special’ in Excel to Apply Number Format in Millions with Comma

STEPS:

• Select any cell and enter 1000000. We have selected Cell E5.
• Press Ctrl + C to copy.

• 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.
• Click OK to proceed.

• You will see results as shown in the image 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