Excel Number Format for Millions With One Decimal Place

How-to basics · Updated November 19, 2026

To show a number in millions with one decimal place in Excel, apply the custom number format #,##0.0,,. Select the cells, press Ctrl+1 on Windows, choose Custom, enter the format in the Type box, and select OK; to add an M suffix, use #,##0.0,,"M".

What the millions format does

The format changes how a value looks in a cell, not the value Excel stores. The two commas at the end of #,##0.0,, scale the displayed number down by two groups of three digits: one comma represents thousands, and the second represents millions. The format also asks Excel to show one digit after the decimal point.

For example, a cell containing 1,234,567 appears as 1.2 with the basic format, or 1.2M with the suffix version. A value of 25,000 appears as 0.0M because it is only 0.025 million and rounds to one decimal place. The underlying cell still contains its original number, so a formula that adds that cell uses 1,234,567—not 1.2.

This is useful for financial summaries, dashboards, reports, and charts where showing every digit would make a table harder to scan. It is a display choice, not a calculation or a conversion of the data.

Apply the custom format in Excel for Windows

  1. Select the cells you want to display in millions. To format an entire column, click its column letter; to select separate ranges, hold Ctrl while selecting them.
  2. Press Ctrl+1 to open Format Cells. You can also right-click the selection and choose Format Cells.
  3. On the Number tab, select Custom in the Category list.
  4. Click the Type box, enter #,##0.0,,"M", and select OK. Use #,##0.0,, instead if you do not want the M.

The commas displayed between thousands in the format code are grouping separators; the two commas after the decimal-place placeholder perform the scaling. Do not put a space between the final commas and the M literal unless you want a visible space in the result. To show one, use #,##0.0,," M".

To format a whole range quickly, select the range before opening Format Cells. The setting applies to every selected cell, including cells you fill later within that formatted range. It does not automatically apply to new cells outside the selection.

Use the format on Mac or Excel for the web

In Excel for Microsoft 365 on Mac, select the cells and press Command+1 to open the Format Cells dialog. Choose Custom, enter the same format code, and confirm. The comma scaling principle and the custom code are the same; the main difference in these steps is the Mac shortcut.

Excel for the web has a more compact interface, and available custom-format controls can differ with the version and workbook. Look for the number-format menu on the Home tab and its additional number-format options, then choose Custom if it is available. If the interface does not let you create or edit the required code, open the workbook in the desktop Excel app, apply the format there, and save it. Avoid replacing the values with shortened numbers just to make the web display compactly.

How the commas and decimal place work

Excel custom number formats use placeholders to describe how values should appear. In this code, # stands for an optional digit, 0 requires a digit to appear, and the period marks the decimal position in the common U.S. format convention. The final commas are scaling instructions, not characters to print.

  • #,##0.0 displays a full number with thousands grouping and one decimal place.
  • #,##0.0, displays the number divided by 1,000, so it is shown in thousands.
  • #,##0.0,, displays the number divided by 1,000,000, so it is shown in millions.
  • #,##0.0,,"M" adds a literal M after the scaled display.

For instance, 8,765,432 displays as 8.8M; 42,300,000 displays as 42.3M; and 987,654,321 displays as 987.7M. Excel rounds the display to the requested decimal precision. If the scaled value is 8.76, the one-decimal display is 8.8M. The original value remains available for formulas and for changing the number format later.

Choose whether to include an M suffix

The suffix makes the unit explicit, which is valuable when a report might be read without its heading or surrounding explanation. Without it, 42.3 could mean 42.3 million, 42.3 dollars, or simply 42.3 units. With the suffix, the scale is visible in every cell.

On the other hand, a heading such as Revenue (millions) can make the unit clear without repeating M in every cell. In that case, #,##0.0,, creates a less cluttered column. Choose one consistent way to identify the scale; do not use an M suffix and a heading such as Revenue ($ millions) if that combination could make the unit ambiguous.

For currency, you can include a currency symbol in the custom format. For example, $#,##0.0,,"M" displays a dollar sign, a scaled number, and M. This changes only the appearance; it does not establish that the source data is in U.S. dollars. Use the symbol and label that correctly describe the data.

Negative values, zeroes, and blank-looking cells

A basic one-section format such as #,##0.0,,"M" will generally show negative numbers with a minus sign and zero as 0.0M. You can define separate appearances for positive values, negative values, and zero by separating format sections with semicolons. For example, #,##0.0,,"M";(#,##0.0,,"M");- displays positive values normally, negative values in parentheses, and zero as a dash.

Custom formats can contain up to four sections: positive numbers, negative numbers, zero, and text, in that order. The example uses the first three. The dash in the zero section is literal text, so it is displayed instead of a numeric zero. If you also need to control text display, add a fourth section after another semicolon, such as @ for the cell’s text. Be deliberate with this option: a dash can mean zero in the report, while an empty cell means no value was entered.

One-decimal rounding can make small nonzero amounts look like zero. For example, a value of 20,000 is 0.02 million and can display as 0.0M. If that distinction matters, use two decimal places, show the underlying figures in a detail column, or choose thousands as the display unit. Do not interpret a rounded 0.0M as proof that the source value is exactly zero.

Common problems and how to fix them

  • The value looks unchanged: Check that the format ends with two scaling commas. A single trailing comma scales by thousands, not millions.
  • The display shows an M but the number is wrong: Confirm the source value is a number, not text. Formatting cannot scale text stored in a cell.
  • The number is too small: The cell may already contain values expressed in millions. Applying the scaling format again would divide the displayed value by another million.
  • There is no decimal digit: Check that the code includes .0, not just . or .#. The zero placeholder requires one decimal digit.
  • Excel rejects or changes the code: Regional settings can affect how decimal and grouping separators are entered or shown. Try creating the custom format through the dialog and follow the separators Excel uses for your locale.
  • Other cells do not match: Number formats are attached to cells, not to an entire dataset automatically. Apply the format to the full relevant range.

Also check whether a formula, import, or paste operation has changed the cell’s format. Some data-refresh workflows and pasted content can bring in different formatting. If the display changes after a refresh, reapply the format to the affected range and review the settings of the source or refresh process.

Formatting versus dividing the data

A custom number format is usually the safer choice when you want a compact view but need the exact values for totals, lookups, exports, or later analysis. For example, if a cell contains 1,234,567 and is displayed as 1.2M, a SUM formula still adds 1,234,567. Other calculations likewise use the stored value.

Dividing each source value by one million is a different operation. It changes the data or creates a separate scaled result, which may be appropriate when a deliverable specifically requires values stored in millions. But it can affect totals, comparisons, and downstream formulas if users assume the cells still contain full values. If you need both, keep the original figures and create a clearly labeled separate presentation range.

Custom number formats are also different from the TEXT function. A custom format keeps a numeric cell numeric; a formula that converts a number to text produces text, which may not behave like a number in later calculations. A chart can use the original numeric values while its labels or axis are formatted to show a compact scale, but check the chart’s own number-format settings if changing the worksheet cells does not change the chart appearance.

Frequently Asked Questions

What is the exact Excel format code for millions with one decimal?
Use #,##0.0,, for a number without a suffix, or #,##0.0,,"M" to append M. The two trailing commas scale the display to millions, and .0 requests one decimal place.

Does this format change the actual number?
No. It only changes the displayed value. A cell formatted as 1.2M can still contain 1,234,567, and formulas use that stored number.

How do I show negative millions in parentheses?
Use #,##0.0,,"M";(#,##0.0,,"M");- for positive values, negative values in parentheses, and zero as a dash. Remove the final zero section if you want zero displayed using the ordinary positive format instead.

Why does 25,000 display as 0.0M?
It equals 0.025 million, which rounds to one decimal place as 0.0M. Use more decimal places or show the value in thousands if small amounts need to remain visible.

Can I use the same format for currency?
Yes. For example, $#,##0.0,,"M" adds a dollar sign. Make sure the source values really are in dollars and label the report clearly; the format itself does not convert currencies.

Practical checks before sharing a report

Before sending a workbook, compare a few displayed cells with their full values in the formula bar. Check a value over one million, a negative value if present, a zero, and a small nonzero value. This catches misplaced commas and reveals whether rounding hides meaningful amounts.

Finally, label the scale clearly in a heading, suffix, or note, and make sure totals are calculated from the intended underlying values. If someone needs the exact figures, provide an unscaled detail column or explain how to remove the display format. The format is a compact presentation layer: useful precisely because it shortens what readers see without discarding the original numbers.

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.