Excel Custom Number Format: Round Values to Millions

Formulas & functions · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, apply the custom number format #,##0,,"M" to display numbers in millions rounded to the nearest whole million. The format changes how a number looks, not its stored value: 1,234,567 displays as 1M, while formulas still use 1,234,567.

What a millions number format does

A custom number format is a display rule attached to a cell. Excel keeps the original number in the cell and uses the format when showing it in the grid. This makes a format useful for compact reports: a column can show a series such as 2M, 18M, and 126M without replacing the underlying amounts with shortened numbers.

In #,##0,,"M", the number placeholders and commas do two jobs. The #,##0 portion displays a whole number with thousands grouping if needed, and the two commas after the final zero scale the displayed value down by 1,000 twice—that is, by 1,000,000. The quoted M is a literal character appended to the displayed result.

For example, the format displays 1,234,567 as 1M and 12,600,000 as 13M. The second result is rounded for display; Excel has not changed 12,600,000 to 13,000,000. If you select the cell, the formula bar still shows the actual value or formula.

Apply the format in Excel for Windows

In the desktop version of Excel for Microsoft 365 on Windows, select the cells you want to format, open the Format Cells dialog with Ctrl+1, and choose the Custom category on the Number tab. Enter #,##0,,"M" in the Type box, then select OK. The format is applied to all selected cells.

You can also reach the dialog from the ribbon: select Home, open the Number Format drop-down in the Number group, and choose More Number Formats. In the Format Cells dialog, choose Custom and enter the format code. If Excel adds a format to the list after you apply it, you can select that entry for other cells in the workbook.

  • To show one decimal place, use #,##0.0,,"M". A value of 1,234,567 displays as 1.2M.
  • To show two decimal places, use #,##0.00,,"M". The same value displays as 1.23M.
  • To include a currency symbol, try $#,##0.0,,"M". The exact currency symbol and decimal punctuation may depend on your regional settings.

On Excel for Mac, select the cells and use Command+1 to open Format Cells, then choose Custom and enter the code. Excel for the web may display custom formats already applied in a workbook, but creating or editing a custom format is best done in the desktop app when the Custom controls are unavailable.

How the scaling commas work

Commas in a number format are not all separators. A comma between digit placeholders, as in #,##0, groups digits in threes. A comma placed after the final digit placeholder scales the displayed number down by a factor of 1,000. Two scaling commas divide the displayed value by 1,000,000; three divide it by 1,000,000,000.

That distinction explains why a format for thousands is often written as #,##0,"K", while a format for millions uses #,##0,,"M". The grouping comma makes a full-size number easier to read, while the commas at the end set the display scale. The K and M are labels only; Excel does not infer or convert units from those letters.

A few examples make the effect clearer. With #,##0,,"M", 850,000 displays as 1M, 1,499,999 displays as 1M, and 1,500,000 displays as 2M under ordinary number-format rounding. With #,##0.0,,"M", those same values display as 0.9M, 1.5M, and 1.5M. The smaller the displayed precision, the more detail is hidden by rounding.

Choose how many decimal places to show

Choose precision based on what a reader needs to compare. Whole millions make a dense executive summary easy to scan, but values near one another can look identical. If amounts of 1.24 million and 1.43 million both matter, one decimal place distinguishes them as 1.2M and 1.4M. Two places give more detail, at the cost of a wider column and more visual clutter.

Use a zero placeholder when you want a fixed number of decimals: 0.0,,"M" always displays one decimal place, including trailing zeroes. Use a question-mark or hash placeholder when you want to control spacing or suppress optional zeroes. For many ordinary reports, #,##0.0,,"M" is a straightforward compromise; for financial statements, follow the precision and unit conventions used in the rest of the report.

Also decide whether to include a label in every cell. Repeating M makes an isolated number understandable, but a heading such as “USD millions” can reduce repetition across a clearly labeled table. If you omit M from the cell format, make the scale unmistakable in the column heading so readers do not mistake 1.2 for $1.20 or 1.2 dollars.

Format negative numbers and zeros

Excel custom number formats can use separate sections for positive numbers, negative numbers, zero, and text, separated by semicolons. For example, #,##0,,"M";(#,##0,,"M");"-" shows positive values as 12M, negative values in parentheses as (12M), and exact zero as a dash. The first section applies to positive values, the second to negative values, and the third to zero.

When you customize these sections, keep the scale and precision consistent. A negative value of -12,400,000 will display as (12M) with that example; the underlying value remains negative 12,400,000. If the workbook convention uses a minus sign instead of parentheses, use a negative section such as #,##0,,"M";-#,##0,,"M";"-".

A zero section affects only a value that is exactly zero. A small nonzero amount, such as 250,000, still formats as 0M with no decimal places, rather than as a dash. If that is misleading, show one or more decimal places, use a conditional format, or choose a display convention that communicates that the amount is below the reporting threshold.

What formatting changes—and what it does not

The most important limitation is that formatting does not round the data used in calculations. If a cell contains 1,234,567 and displays 1M, a total, average, or comparison still uses 1,234,567. That is normally desirable: the report is compact while calculations retain their precision.

It also means that a displayed result may appear not to match arithmetic done by eye. For example, three rows may each display 1M, although their exact values total 2.6 million and a formatted total displays 3M. The visible row labels are rounded separately; the total is calculated from the exact amounts and rounded afterward. This is not necessarily an error, but it can surprise readers.

Custom formatting does not turn a number into text, so sorting and numeric formulas continue to work normally. A chart based on the cells also uses numeric values, although chart labels and axes may need their own number formatting. When sharing data with another program, remember that the custom format may not travel with the numeric value; CSV files, for example, do not preserve Excel cell-format rules.

Custom formats compared with formulas and rounding

Use a number format when the goal is a compact appearance and the exact values should remain available for calculations. Use a formula that rounds or divides when you deliberately need a separate scaled value—for instance, when preparing a data export whose receiving system expects values in millions. Keep the original data if later calculations may need full precision.

A formula using Excel’s TEXT function can create a text label for a report. With the value 1,234,567 in A2, =TEXT(A2,"#,##0,,")&"M" returns the text 1M. This differs from cell formatting: the result is text, so it is not a numeric amount for ordinary arithmetic, and changing the display format of that result will not restore the original number. For interactive worksheets, applying a custom format is usually more flexible.

Do not confuse rounding for display with the ROUND function or with a change to calculation settings. The custom format only controls what is shown. If a downstream calculation must use rounded millions, create an explicit calculation for that purpose and verify whether it should round, truncate, or retain decimals. Those choices can produce different results, especially when several rounded values are added.

Common problems and how to fix them

  • The number looks unchanged: Check that the code has two scaling commas after the final digit placeholder, and that you applied it to the intended cells. A format with only one scaling comma displays thousands, not millions.
  • The M appears as a formatting instruction or is missing: Put the literal letter in double quotes, as in "M". In the Type box, enter the full code rather than just the letter.
  • The result shows 0M: The value may be below half a million when no decimals are displayed. Add a decimal place or use a different reporting scale if small values need to remain visible.
  • Decimals use unexpected punctuation: Excel follows regional settings for decimal and thousands separators. If a code does not behave as expected, check the separators shown in the Format Cells dialog and use the local conventions.
  • A total does not equal the visible row amounts: The cells are rounded only for display. Check the formula bar or temporarily use a format with more decimal places to inspect the full numbers.

Frequently Asked Questions

What custom format rounds a number to the nearest million?
Use #,##0,,"M" for whole millions, or #,##0.0,,"M" for one decimal place. These formats round the display; they do not alter the stored number.

How do I show 1.2 million as 1.2M in Excel?
Apply #,##0.0,,"M" to the cell. For a value of 1,200,000, Excel displays 1.2M.

Will this format change the value used in a sum?
No. A sum uses the full stored values, not the rounded values shown on screen. Use a separate calculation if the calculation itself must use rounded amounts.

Can I show a dollar sign and millions?
Yes. A format such as $#,##0.0,,"M" displays a value of 1,200,000 as $1.2M in a typical US regional setup. Check the workbook’s currency and regional conventions before using it in a shared report.

Can I create this format in Excel for the web?
Excel for the web can show many formats applied in a workbook, but its available custom-format editing options may differ. If you cannot enter the code there, open the workbook in Excel for Windows or Mac and use Format Cells > Custom.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.