Excel Custom Number Format for Millions

How-to basics · Updated November 19, 2026

To display Excel values in millions without changing the stored numbers, apply the custom number format #,##0.0,,"M". The two commas after the decimal placeholder scale the display by one million, so 1,250,000 appears as 1.3M while the cell still contains 1,250,000.

What a millions format does

An Excel custom number format changes how a value is shown, not the value itself. This is useful when a report contains large amounts and readers need to compare them at a glance: a column of figures such as 2,450,000, 8,100,000, and 14,300,000 can be shown as 2.5M, 8.1M, and 14.3M. The shorter display takes up less space while preserving the original precision for calculations.

Number formats do not turn a number into text. A cell displaying 2.5M can still participate in arithmetic, sorting, charting, and functions such as SUM. The formula bar normally shows the underlying value when the cell is selected. If you need an actual converted value—for example, a separate column whose values are 2.5, 8.1, and 14.3—use a calculation instead of relying on formatting.

How to apply the custom format

In Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1, choose the Number tab, select Custom, enter a format in the Type box, and select OK. For example, enter #,##0.0,,"M" to show one decimal place and an M suffix. The same dialog is available from the Number group on the Home tab by opening the Format Cells dialog. On Mac, select the cells and use Format > Cells, then choose Custom; keyboard shortcuts can vary with the keyboard and system settings.

  1. Select the cells containing the values you want to display in millions.
  2. Open Format Cells and select Custom.
  3. Enter the desired code in Type, including any quoted suffix such as "M".
  4. Check the sample preview, then select OK.

You can also enter a new code for a selected cell in the Custom category. Excel saves custom formats in the workbook, so a format you create may be available to other cells in that workbook. If you send the file to someone else, the cells retain the format when opened in Excel.

Examples of formats for millions

Choose a code based on the precision and appearance your report needs. These examples assume the displayed values are nonnegative unless a negative format is included:

  • #,##0,,"M" displays 1,250,000 as 1M. It rounds to a whole million.
  • #,##0.0,,"M" displays 1,250,000 as 1.3M and 12,345,000 as 12.3M.
  • #,##0.00,,"M" displays 1,250,000 as 1.25M.
  • 0.0,," million" displays 1,250,000 as 1.3 million. The word is quoted so Excel treats it as a literal suffix.
  • $#,##0.0,,"M" displays 1,250,000 as $1.3M. Use the appropriate currency symbol for the report.

The # placeholder allows digits to appear when present, while 0 forces a digit to appear. The comma between # and 0 in a pattern such as #,##0 is a thousands-grouping symbol; the commas after the final digit placeholder are scaling symbols. These uses look similar but have different jobs.

Why two commas scale by one million

In a custom number format, a comma placed immediately after the last digit placeholder divides the displayed number by 1,000. A second scaling comma divides it by another 1,000. That is why two commas represent division by 1,000,000. For example, the stored value 7,500,000 is displayed as 7.5 with 0.0,,, or as 7.5M with 0.0,,"M".

By contrast, the comma in #,##0 groups thousands and does not scale the number. The complete pattern #,##0.0,,"M" combines grouping for any remaining displayed digits, one decimal place, scaling by a million, and a literal M. Once scaled, the result may no longer need a thousands separator, but the grouping pattern is still useful when the same code is adapted for other scales or values.

One common mistake is to type the letter M without quotes. A letter can have a special meaning in some number-format contexts, and quoting the suffix makes clear that it should be displayed as literal text. If the code is entered using a different regional version of Excel, the punctuation shown in the Type box may differ because decimal and list separators can be locale-dependent. Follow the format syntax displayed by your Excel installation if it changes those characters.

Rounding and choosing decimal places

Excel rounds the displayed result to the number of decimal places specified; it does not round the stored value. With one decimal place, 1,250,000 divided for display by one million is 1.25, which appears as 1.3M under normal rounding. A value of 1,240,000 appears as 1.2M. If the report needs finer distinctions, use two decimals; if it needs a compact executive summary, whole millions may be adequate.

Formatting can make nearby numbers look identical. For example, 1,240,000 and 1,260,000 both display as 1.2M and 1.3M respectively with one decimal, but smaller differences can disappear at the chosen precision. Before distributing a report, check whether the rounding hides a meaningful change. Do not interpret the displayed figure as the exact amount: the cell retains the full number, and selecting it reveals that number in the formula bar.

Rounding also affects values near zero and near a million boundary. A value slightly below one million can display as 1.0M when one decimal place is used, even though it is not actually one million. If that distinction matters, show more decimals or use an exact-value column alongside the compact summary.

Show negative values and zeros clearly

A custom format can define separate patterns for positive numbers, negative numbers, and zero, with each section separated by a semicolon. For example, #,##0.0,,"M";[Red](#,##0.0,,"M");"—" shows positive values with an M suffix, negative values in red parentheses, and zero as a dash. The number of sections matters: the first applies to positive values, the second to negative values, and the third to zero. A fourth section can control text values.

If you want text in cells to remain visible while applying those three numeric treatments, add a fourth section containing the text placeholder, as in #,##0.0,,"M";[Red](#,##0.0,,"M");"—";@. These formats affect presentation only. A red negative number remains negative, and the dash for zero does not mean the cell is blank. For financial or operational reports, choose conventions that readers can distinguish and document what the suffix means.

Custom format versus a formula

Use a custom number format when the goal is a compact display and calculations should continue to use the original values. Use a formula when you need a genuinely scaled number for another calculation, data export, or a separate presentation column. For example, with the original amount in A2, the formula =A2/1000000 returns a numeric value in millions; if A2 contains 1,250,000, the result is 1.25. You can then apply a format that adds an M suffix, or label the column as amounts in millions.

A formula such as =TEXT(A2/1000000,"0.0")&"M" can create a text label such as 1.3M, but the result is text rather than a number. That can complicate later arithmetic and charting. Prefer number formatting if the output needs to remain numeric. Also avoid dividing the original data and then applying a scaling format to it: doing both would scale the display twice.

Common problems and how to fix them

  • The value does not change: That is expected. A number format changes appearance, not the stored value. Check the formula bar or use the cell in a calculation to verify the underlying amount.
  • The result is too small: Confirm that the format has two scaling commas after the number placeholders. One comma scales to thousands; two scale to millions.
  • The M appears as a strange symbol or is missing: Put the literal suffix in straight double quotation marks, as in 0.0,,"M". Re-enter the code in the Custom Type box rather than adding the suffix to the cell value.
  • Small amounts display as zero: A whole-million format rounds values below half a million to zero. Add decimal places or use a thousands format for smaller values.
  • Some cells do not change: They may contain text rather than numeric values, or they may have a different format applied. Check the underlying contents and select the full range before applying the code.
  • The format looks different on another computer: Regional settings can change decimal and grouping punctuation. The underlying value and scaling intent remain the same, but check the rendered result in the recipient’s Excel environment.

Using millions formats in charts and reports

Compact formats can make worksheets and charts easier to read, but readers need to understand the scale. A chart axis labeled 1.0M, 2.0M, and 3.0M is usually clear; a table column headed “Revenue” with figures like 1.2 may not be. Use a heading such as “Revenue (millions)” if the suffix is omitted, or retain the M suffix on each value when the data may be copied out of context.

For charts, format the axis labels or data labels themselves if the chart does not inherit the cell formatting as expected. Depending on the chart and how its source data is configured, a chart may use its own number format. Check the chart after changing the worksheet cells, particularly if the chart is intended for a presentation or printed report. Do not replace the source data with rounded text merely to shorten the labels unless the chart specifically requires text.

Frequently Asked Questions

What is the Excel custom format for millions? Use #,##0,,"M" for whole millions, or #,##0.0,,"M" for one decimal place. The two commas scale the displayed number by one million.

How do I show 1,000,000 as 1M in Excel? Apply 0,,"M" to the cell. The stored value remains 1,000,000, while the display shows 1M.

Does a custom number format change the actual number? No. It changes only the display. Formulas, sums, and other calculations continue to use the original stored value.

How do I show millions with a dollar sign? Use a currency symbol in the format, such as $#,##0.0,,"M", for a display like $1.3M. Choose the symbol and reporting convention appropriate to your data.

Can I show negatives in parentheses and zeros as dashes? Yes. Use sections separated by semicolons, such as #,##0.0,,"M";(#,##0.0,,"M");"—". The sections control positive values, negative values, and zero, in that order.

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.