Excel Number Format to Millions: Steps and Examples

How-to basics · Updated November 19, 2026

To show Excel values in millions without changing the stored numbers, select the cells, press Ctrl+1, choose Custom, and enter #,##0.0,,"M". Click OK: for example, 1,234,567 will display as 1.2M while remaining 1,234,567 for calculations.

What a millions number format does

Excel custom number formats change how a value appears in a cell, not the value itself. In the format #,##0.0,,"M", the two commas at the end scale the displayed number down by two groups of three digits—one million—while the quoted M adds a label. The decimal placeholder asks Excel to show one decimal place.

This is useful when a report contains large sales, budgets, populations, or other totals and showing every digit would make a table hard to scan. A column of values such as 1,234,567, 8,950,000, and 125,000,000 can instead appear as 1.2M, 9.0M, and 125.0M. The underlying values are still available for formulas, sorting, charts, and summaries.

The distinction matters: a number format is a presentation choice, not a conversion. If you copy a formatted cell into a formula, Excel uses the full value. If you export or share the workbook, make clear that the figures are presented in millions so readers do not mistake 1.2M for the exact stored value 1.2.

Apply the format in Excel for Windows

In Microsoft Excel for Microsoft 365 on Windows, use a custom format to control both the scale and the suffix. You can apply it to a selected range, a whole column, or a chart data table.

  1. Select the cells you want to format. To format an entire column, click its letter at the top of the worksheet.
  2. Press Ctrl+1 to open Format Cells. Alternatively, on the Home tab, open the Number Format dropdown in the Number group and choose More Number Formats.
  3. On the Number tab, select Custom in the Category list.
  4. In the Type box, enter #,##0.0,,"M". Use the same number of decimal places throughout a column for consistent presentation.
  5. Check the sample shown in the dialog, then click OK.

For whole millions with no decimal places, use #,##0,,"M". With that format, 1,234,567 displays as 1M because Excel rounds the scaled value to the nearest whole million. If you want to truncate rather than round, number formatting is not the right tool; use a calculation that deliberately changes the value instead.

On a Mac, select the cells and press Command+1 to open Format Cells, then choose Custom and enter the format code. The dialog’s appearance may differ slightly from Windows. Excel for the web has a more limited formatting interface; if you cannot create the custom code there, open the workbook in the desktop app. A custom format already saved in a workbook may display in the web app, but check the result before distributing it.

Choose how many decimal places to show

The right precision depends on the audience and the size of the values. A decimal place represents one tenth of a million, or 100,000 units. Two decimal places represent increments of 10,000 units. For example, 1,234,567 becomes 1.2M with one decimal place and 1.23M with two.

  • #,##0,,"M" shows whole millions, such as 1M or 12M.
  • #,##0.0,,"M" shows tenths of a million, such as 1.2M or 12.3M.
  • #,##0.00,,"M" shows hundredths of a million, such as 1.23M or 12.35M.

Excel rounds the displayed result to the chosen number of decimal places. A value of 1,250,000 displayed with no decimals becomes 1M or 2M depending on the rounding boundary and the exact value; in this case it rounds to 1M under Excel’s standard display rounding for this format. With one decimal, it displays as 1.3M. The exact stored amount is not rounded by this display operation. If a report needs a specific rounding convention, verify it with representative values and use a formula or separate calculation when the rounded amount itself must be used downstream.

Useful format variations

You can adjust the format code for readability and reporting conventions. The quoted suffix is literal text, so you can use a space before M if you prefer a display such as 1.2 M. For instance, #,##0.0,," M" includes that space. The suffix does not convert the value into a different unit; it is simply a label.

To show negatives in parentheses, zeros as a dash, and text unchanged, use a four-section format. With positive values in the first section, negative values in the second, zeros in the third, and text in the fourth, enter #,##0.0,,"M";(#,##0.0,,"M");-;@. For example, a negative 1,234,567 displays as (1.2M), zero displays as a dash, and text remains visible. Use this only if those conventions suit the workbook; the format changes appearance, not the meaning of a negative amount or zero.

Custom formats can also use color codes such as [Red], but color alone should not carry essential meaning. A reader may print in grayscale or have a visual accessibility need. When the sign or status matters, include a clear label or another visual cue as well.

Formatting versus dividing the values

Use formatting when you want readable displays but need the original figures to remain intact. Use a calculation when the resulting cells should actually contain values measured in millions—for example, because another system expects values such as 1.234567 rather than 1,234,567.

With the original amount in A2, enter this in B2 to calculate the amount in millions: =A2/1000000. If A2 contains 1,234,567, B2 returns 1.234567. You can then format B2 with a suitable number of decimals and, if appropriate, add an M suffix using a format without scaling commas, such as 0.0"M", to display 1.2M. This formula makes B2 a different numeric value from A2; it is not merely a visual change.

A common mistake is dividing by 1,000,000 and also applying the two scaling commas. That scales the result twice, making 1,234,567 display close to 0.0M instead of 1.2M. Choose one approach: keep the original value and use the scaling commas, or divide the value and use a suffix-only format.

What happens to calculations, charts, and sorting

Because number formatting leaves the stored number unchanged, formulas such as SUM and AVERAGE continue to use the full values. A sum of 1,234,567 and 2,000,000 remains 3,234,567 even if the cells display as 1.2M and 2.0M. The formula bar also shows the underlying value when you select a cell, unless the formula bar is displaying a formula.

Sorting numeric cells still sorts by their actual numeric values, not by the shortened text shown on screen. Charts generally use the actual values too. If a chart axis should be labelled in millions, set its number format separately: select the axis, open Format Axis, find Number, and apply an appropriate custom format. The exact options can vary by chart type and Excel version.

Formatting a cell does not add an M to the value stored in the cell. In most calculations that is an advantage. But if another person needs to interpret a printed table, provide a heading such as “Revenue (millions)” or a note explaining that values are displayed in millions. A suffix on each displayed number helps, but context prevents confusion about units and precision.

Common problems and how to fix them

  • The value appears unchanged. Check that the code has two trailing commas after the final digit placeholder and that the cell is numeric. Text that looks like a number will not scale as a number.
  • The M appears without scaling. The M by itself is only a literal suffix. Include the two scaling commas before the quoted suffix.
  • The result has too many or too few decimals. Add or remove zero placeholders after the decimal point. In a custom number format, 0 forces a digit to appear, while # allows a digit to be omitted when it is not needed.
  • The displayed value looks rounded. That is expected: the format rounds the display to the chosen precision. Increase decimal places if readers need more visible detail, or inspect the formula bar for the full stored amount.
  • The code is rejected or looks different. Check quotation marks, commas, and semicolons. Excel may use regional settings that affect decimal and list separators, and the custom-format dialog may show localized conventions. Use the code style accepted by your Excel installation.
  • Some cells do not change. Verify that the entire intended range was selected and that the cells are not stored as text. You can convert numeric text to numbers, for example with Excel’s warning indicator or Text to Columns, before applying the custom format.

Frequently Asked Questions

How do I show millions in Excel without changing the value?
Select the cells, open Format Cells with Ctrl+1 on Windows, choose Custom, and use #,##0.0,,"M". The number is scaled only for display; Excel retains the original value for calculations.

What custom format shows millions with no decimal places?
Use #,##0,,"M". Excel divides the displayed value by one million and rounds it to a whole number, so smaller amounts may appear as 0M and values near a rounding boundary may display as the next million.

How do I display a dollar sign with millions?
For values already stored as dollars, use $#,##0.0,,"M" to display an amount such as $1,234,567 as $1.2M. Confirm that the source values are in dollars; formatting cannot convert dollars to another currency.

Can I show negative millions in parentheses?
Yes. Use a format with a separate negative section, such as #,##0.0,,"M";(#,##0.0,,"M"). The first section controls positive values and the second controls negative values.

Why does Excel still show the full number in the formula bar?
The formula bar shows the stored value or formula, while the worksheet cell shows the formatted display. This is normal and confirms that a display-only format has not replaced the original amount.

Can I change the format back?
Yes. Select the cells and choose another format from Home > Number, or open Format Cells and choose General. This changes how the values appear; it does not restore values if you previously divided or overwrote them with a calculation.

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.