Number Formatting feature in Excel allows modifying the appearance of cell values, without changing their actual values. Currency formatting with dollar signs ($), or highlighting negative values with red are common examples. Another advantage of this feature is the ability to add thousands separators without changing the cell values. In this article, we're going to show you how to format numbers in Excel with million separators.

How

Number Formatting is a versatile feature that comes with various predefined format types. You can also create your own structure using a code. If you would like to format numbers in millions, you need to use thousands separator in the format code with a proper number placeholder. For example; 0,, represents any number with its first millions part hidden.

42,000,000 will be displayed as 42

Below are some common placeholders:

Placeholder	Description
#	Placeholder for digits (numbers) and does not add any leading zeroes.
0	Placeholder for digits (numbers) and add any leading zeroes.
.	Placeholder for the decimal place.
,	Thousands separator. Apply twice (,,) for millions.

Tip: Thousands and decimal separators can vary based on your local settings. For example, while dot (.) is usually a thousand separator in North America, comma (,) is a decimal separator in Europe.

And here is samples:

Steps

Select the cells you want format.
Press Ctrl+1 or right click and choose Format Cells… to open the Format Cells dialog.
Go to theNumber tab (it is the default tab if you haven't opened before).
Select Custom in the Category list.
Type in #,##0.0,, "M" to display 1,500,800 as 1.5 M
Click OK to apply formatting.