Excel Number Format to Round Values to Millions Easily

Formulas & functions · 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 scale the display by one million, so 1,234,567 appears as 1.2M; if you need to change the underlying value by rounding it to the nearest million, use a formula such as =ROUND(A2,-6) instead.

Apply a millions format in Excel

In Microsoft Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1, and choose the Custom category in the Format Cells dialog. Enter #,##0.0,,"M" in the Type box and select OK. The example adds one decimal place and an M suffix. For whole millions, use #,##0,,"M"; 1,234,567 then appears as 1M.

You can also open the same dialog from the Home tab: in the Number group, select the small dialog box launcher, then choose Custom. On a Mac, select the cells and press Command+1 to open Format Cells; the Custom category and format-code approach work there as well. In Excel for the web, open the number-format menu on the Home tab and choose More Number Formats to access custom formats, although the dialog can look different from the desktop version.

  1. Select the cells or entire column to format.
  2. Open Format Cells and choose Custom.
  3. Enter a format code, such as #,##0.0,,"M".
  4. Check the sample preview, then select OK.

How the commas scale the display

Excel custom number formats use commas for two different purposes. A comma between digit placeholders can mark thousands grouping, as in #,##0. A comma immediately after the number-format placeholders scales the displayed value down by a factor of 1,000. Two scaling commas divide the displayed value by 1,000,000. They do not insert text or divide the value stored in the cell.

For example, with the format #,##0.0,,"M", a stored value of 1,234,567 is displayed as 1.2M, and 25,800,000 is displayed as 25.8M. The first comma in #,##0.0 groups thousands in the scaled display; the two commas after the decimal placeholder perform the million scaling. The letter M is literal text, enclosed in quotation marks so Excel displays it as a suffix rather than interpreting it as a format character.

A number format changes how a value looks, not the value used in calculations. If a cell contains 1,234,567, formulas that refer to it still use 1,234,567, even when the cell shows 1.2M. This distinction is why a display format is usually preferable for reports: you retain the original precision and can change the presentation later.

Choose decimal places and labels

The right format depends on how much detail readers need. A single decimal place is compact but rounds the display to tenths of a million, which is increments of 100,000. Two decimal places show increments of 10,000. No decimal places give a quick overview but can hide substantial differences between values.

  • #,##0,,"M" displays 1,234,567 as 1M.
  • #,##0.0,,"M" displays it as 1.2M.
  • #,##0.00,,"M" displays it as 1.23M.
  • #,##0.0,," million" displays it as 1.2 million.

These display results are rounded to the chosen number of decimal places; they are not truncated. If your audience might mistake M for another unit, spell out million in the format or explain the units in a column heading, such as “Revenue (millions of dollars).” A heading can also prevent repeating a suffix in every row, though a suffix in each cell can be clearer when values are copied or viewed separately.

Display rounding versus changing the value

“Round to millions” can mean either show a number in million-sized units or replace it with the nearest multiple of one million. A custom format does the first: it scales and rounds what appears on screen but leaves the original amount intact. If you sort, sum, or calculate percentages using those cells, Excel uses the original values.

To store a rounded amount in a separate cell, assume the original amount is in A2. Enter =ROUND(A2,-6) in another cell. For 1,234,567, this returns 1,000,000, the nearest million. A negative second argument tells ROUND to round to a position to the left of the decimal point; -6 means millions. This result is still a full numeric amount, so you can apply a millions display format to it if you want it to appear as 1.0M.

If instead you want a numeric count of millions, with the original value in A2, enter =A2/1000000 in another cell. For 1,234,567, the result is 1.234567. You can then format that result with an appropriate number of decimal places and add an M suffix. This formula changes the scale in the new cell, unlike a custom format, and the new cell no longer contains the original amount.

Format negative numbers, zeros, and small values

A basic format applies the same style to positive and negative numbers, so a negative amount may appear with a minus sign according to the format pattern. Custom formats can have separate sections for positive numbers, negative numbers, zero, and text, in that order, with sections separated by semicolons. For instance, #,##0.0,,"M";-#,##0.0,,"M";"—" shows negatives with a minus sign and zero as a dash. The dash is quoted as literal text.

Think about values below one million when choosing the display. With one decimal place, 500,000 shows as 0.5M, but 40,000 shows as 0.0M. That last result may look like zero even though the stored amount is not zero. Add more decimal places, use a thousands-based format for smaller values, or choose a different display for the relevant range. A custom format does not automatically switch from millions to thousands based on each value unless you build a more elaborate conditional format code.

Common formatting problems

  • The value looks unchanged: Check that the scaling commas are after the digit placeholders. In #,##0.0,,"M", the two commas come after the 0.0 pattern.
  • The display is too rounded: Add decimal places, for example changing #,##0,,"M" to #,##0.00,,"M".
  • The suffix is missing or treated unexpectedly: Put literal letters in quotation marks, as in "M" or " million".
  • Calculations still use the full amount: That is expected. A number format changes display only; use ROUND or a division formula if you need a changed numeric result.
  • Different regional separators appear: Excel’s display of decimal and grouping separators can follow the computer’s regional settings. The separator shown in the dialog or worksheet may therefore differ by locale.

Also check whether the cells contain numbers or text. Applying a numeric format to text such as a value imported with a leading apostrophe will not scale it as a number. If an entire column behaves inconsistently, inspect the source data and convert text-formatted numbers to numeric values before relying on the number format.

When to use a format, formula, or chart units

Use a custom number format when you want a readable report while preserving full-precision data for calculations. Use ROUND when downstream calculations should use amounts rounded to the nearest million. Use division by 1,000,000 when a separate cell should genuinely contain a value measured in millions. These choices are not interchangeable: a displayed 1.2M can represent an underlying 1,234,567, a rounded 1,000,000, or a separate numeric value of 1.2, depending on how it was created.

Charts have their own presentation controls. If the goal is to label a chart axis in millions, edit the axis formatting rather than assuming the worksheet cell format will control the axis. In Excel, right-click the chart axis and choose Format Axis; the available Display units options can include Millions. The axis scale and labels are chart settings, while the cells feeding the chart remain unchanged.

Frequently Asked Questions

Does Excel’s millions format change the actual number?
No. Two scaling commas in a custom format affect only the display. A cell containing 1,234,567 still holds that value and uses it in formulas even if it appears as 1.2M.

What format code shows millions with no decimal places?
Use #,##0,,"M". For example, 1,234,567 appears as 1M. Add decimal placeholders before the scaling commas when you need more detail.

How do I round a number to the nearest million in Excel?
If the original value is in A2, use =ROUND(A2,-6) in another cell. A value of 1,234,567 returns 1,000,000; this changes the formula result rather than merely changing its appearance.

Why does 500,000 appear as 0.5M?
That is the expected result when the display is scaled by one million: 500,000 divided by 1,000,000 is 0.5. If a value such as 40,000 appears as 0.0M, increase the decimal places or use a smaller unit.

Can I show the word “million” instead of M?
Yes. Use a quoted literal suffix, for example #,##0.0,," million". The leading space inside the quotation marks separates the number from the word.

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.