Excel Number Format for Millions: Codes and Examples

How-to basics · Updated November 19, 2026

To display a number in millions without changing its stored value, apply the custom number format #,##0,,"M". For one decimal place, use #,##0.0,,"M"; for example, 1,234,567 will appear as 1.2M while Excel still stores 1,234,567.

How commas scale a number in Excel

In an Excel custom number format, a comma can separate thousands in the displayed number, or it can scale the displayed value when it appears after a digit placeholder. Each scaling comma divides the displayed value by 1,000. Two commas therefore display a number in millions: 1,000,000 is shown as 1, and 25,000,000 as 25, before any suffix is added. The number in the cell itself is not divided.

These are the key parts of the code #,##0.0,,"M":

  • # allows a digit to appear when present but does not force a zero in that position.
  • ##0 displays at least one digit to the left of the decimal point.
  • The comma between # and ##0 groups displayed digits in thousands where needed.
  • The two commas after 0 scale the displayed value down by one million.
  • .0 displays one decimal place, and "M" adds a literal M as the unit label.

It can seem odd that the code contains three commas in total. The first is a grouping separator in the format pattern; the final two are scaling commas. The position of the commas, not just their number, determines what they do.

Useful custom number format codes

Choose a code based on how much detail readers need. Enter one of these patterns in Excel’s Custom format box:

  • #,##0,,"M" displays whole millions, such as 1M or 25M.
  • #,##0.0,,"M" displays one decimal place, such as 1.2M or 25.4M.
  • #,##0.00,,"M" displays two decimal places, such as 1.23M.
  • $#,##0.0,,"M" adds a dollar sign, such as $1.2M.
  • 0.0,," million" spells out the unit, such as 1.2 million.
  • #,##0,, scales to millions without adding a suffix; a cell containing 1,200,000 appears as 1.

The last pattern is often confusing because it gives no visible clue that the number has been scaled. A clear label in the column heading, such as “Revenue ($ millions),” can supply the missing context. In most reports, however, an M or “million” suffix is easier to understand.

Apply a millions format in Excel for Windows

In Excel for Microsoft 365 on Windows, select the cells and open Format Cells with Ctrl+1. On the Number tab, select Custom, enter the format code in the Type box, and select OK. The new format appears in the cell without changing the underlying values. You can also reach number-format choices from Home > Number, then open the additional number-format options to get to the Format Cells dialog.

  1. Select the cells, a whole column, or the relevant range.
  2. Press Ctrl+1, then choose the Number tab and Custom.
  3. Type or paste a code such as #,##0.0,,"M" into Type.
  4. Check the Sample preview and select OK.

On Excel for Mac, select the cells and press Command+1 to open Format Cells; choose Custom and enter the code. Excel for the web has number-format options too, but the available controls can differ from the desktop dialog. If you cannot create or edit the custom pattern in your web interface, open the workbook in the desktop app and apply it there.

Choose the right number of decimal places

Decimal places determine how much detail the display preserves; they do not change the cell’s precision. With one decimal place, values are rounded for display to the nearest tenth of a million, which is $100,000 or 100,000 units. For example, 1,234,567 appears as 1.2M, while 1,260,000 appears as 1.3M. With two decimal places, 1,234,567 appears as 1.23M.

Think about the purpose of the report when deciding how many places to show. Whole millions may work for a high-level overview, while one or two decimal places can help compare figures that are close together. More displayed digits do not make the source data more accurate; they only reveal more of its existing value. If small differences matter, check that the source numbers themselves justify showing them.

Rounding can also make a value just below a million appear as 1.0M. For instance, a value of 999,999 formatted to one decimal place in millions rounds to 1.0M. That is a display result, not evidence that the stored value reached one million. Add another decimal place, use a threshold-aware reporting method, or show the full number if that distinction matters.

Format currency and handle negative values

For a dollar amount in millions, $#,##0.0,,"M" displays 1,234,567 as $1.2M. Currency symbols and conventions vary by locale, so use the symbol appropriate to the workbook and its audience. If the report covers a specific currency, state it in the heading or surrounding context rather than assuming every reader will recognize the symbol.

A custom format can include separate sections for positive numbers, negative numbers, zero, and text, in that order, separated by semicolons. For example, $#,##0.0,,"M";-$#,##0.0,,"M";$0.0,,"M";@ assigns a pattern to each category. The first section formats positive numbers, the second negative numbers, the third zero, and @ passes through text. Test more complex codes on representative cells before applying them to a report, especially when accounting conventions require parentheses or a special zero display.

For a simpler negative-number pattern, use #,##0.0,,"M";-#,##0.0,,"M". A negative value then carries a minus sign. Custom formats can also use color directives, but color alone should not be the only way the report communicates whether a value is negative.

Formatting versus changing the values

Number formatting changes how a value looks, not the value used in calculations. If A1 contains 1,234,567 and has the format #,##0.0,,"M", a formula referring to A1 still uses 1,234,567. A sum of similarly formatted cells is calculated from their full stored values, not from the rounded figures shown on screen.

This distinction is useful for charts and calculations: you can keep source values intact while making a report easier to scan. But it can cause confusion when someone copies only what they see or reads a displayed value as an exact amount. Make the unit and rounding level clear in the heading, chart axis, or notes. If a separate column genuinely needs values converted to millions for further work or export, use a deliberate conversion step rather than relying on the cell’s display format.

Also remember that number formats do not affect text. If a cell contains the text “1,234,567” rather than the numeric value 1,234,567, applying a numeric format will not scale it. Convert text to numbers when appropriate, and check for leading apostrophes or import issues if a format seems to have no effect.

Common problems and how to fix them

  • The value still looks unchanged: Check that you selected the right cells and applied the code under Custom. Confirm that the contents are numeric, not text.
  • The result is too small or too large: Count the scaling commas after the digit placeholder. Two scale to millions; one scales to thousands; three scale to billions.
  • The suffix appears as a letter with unexpected behavior: Put literal text in double quotation marks, as in "M" or " million".
  • The result has too many or too few decimals: Add or remove zero placeholders after the decimal point. A zero forces a displayed digit; a hash placeholder can suppress insignificant trailing zeros.
  • Values near a boundary look misleading: Increase the decimal places or show the original value. Display rounding can move a value visually across a threshold.
  • The display is clipped: Widen the column or use fewer decimals. A custom format does not guarantee that a narrow cell has room to show its contents.

For example, to show one decimal place only when needed, a pattern such as #,##0.#,,"M" can omit an unnecessary trailing decimal zero. Compare the preview with values such as 1,000,000 and 1,250,000 before choosing it, since the display should remain consistent with the precision your audience expects.

Frequently Asked Questions

What is the Excel format code for millions?
Use #,##0,,"M" for whole millions or #,##0.0,,"M" for one decimal place. The two scaling commas divide the displayed value by one million; the M is a literal suffix.

How do I show millions with a dollar sign?
Use a pattern such as $#,##0.0,,"M". It displays 1,234,567 as $1.2M. Confirm that the currency symbol and rounding are suitable for the report.

Does the format divide the cell value by one million?
No. It changes only the display. Excel retains the original number for calculations, so a displayed 1.2M can still be the stored value 1,234,567.

How can I display millions without the M?
Use #,##0.0,, to show a scaled number without a suffix, and add a clear unit label such as “Amounts in millions” to the report. Without that label, readers may mistake the display for the original amount.

Can I use the same format for thousands or billions?
One scaling comma divides the displayed value by 1,000, so a pattern like #,##0,"K" shows thousands. Three scaling commas divide by 1,000,000,000; add a quoted suffix such as "B" if the audience understands it. Check local conventions before using abbreviations.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.