To display dollar amounts in millions in Excel without changing the stored values, apply the custom number format "$"#,##0.0,,"M". Select the cells, press Ctrl+1, choose Custom, enter that code in the Type box, and select OK; for example, 1,250,000 will display as $1.3M.
What the millions format does
Excel custom number formats change how a value looks in a cell, not the value itself. The cell containing 1,250,000 still holds 1,250,000 after you apply the millions format. Calculations, sorting, and references continue to use the full number, even though the worksheet displays a shorter label.
The two commas at the end of the format code are scaling commas. Each one tells Excel to display the value divided by 1,000, so two commas display it in millions. The 0.0 portion asks Excel to show one digit after the decimal point, and the quoted M adds a literal M to the display. The quoted dollar sign is a literal currency symbol.
For instance, 2,500,000 appears as $2.5M, while 75,000,000 appears as $75.0M. A custom format is useful for dashboards, financial summaries, and charts where readers need a compact scale but the workbook should retain the exact amounts.
How to apply the custom number format
- Select the cells containing the amounts. To format an entire column, select its letter; you can also select a range of cells.
- On Windows, press Ctrl+1 to open Format Cells. On Mac, press Command+1. Alternatively, use the dialog launcher in the Home tab’s Number group.
- Choose the Number tab, then select Custom in the Category list.
- Click the Type box, enter
"$"#,##0.0,,"M", and select OK.
In some Excel for Mac layouts, the Format Cells dialog may appear slightly different, but the Custom category and Type field serve the same purpose. Excel for the web can display many existing custom formats, but its options for creating or editing custom formats may differ from desktop Excel. If you do not see a Custom format field, open the workbook in Excel for Microsoft 365 on Windows or Mac to create the format.
Choosing decimal places and display precision
The number of zeroes or number signs after the decimal point controls the displayed precision. Use "$"#,##0,,"M" for no decimal places, "$"#,##0.0,,"M" for one, and "$"#,##0.00,,"M" for two. For example, 1,234,567 appears as $1M with no decimals, $1.2M with one decimal, and $1.23M with two.
Those displays round for presentation; they do not round the underlying value. This can matter when a value lies close to a boundary. An amount of 999,999 displayed with one decimal place becomes $1.0M, even though it is still less than one million. If that visual rounding could mislead readers, show another decimal place, add a note such as “amounts in millions,” or display the full figure in a supporting table.
For a compact report with many rows, zero decimal places can improve readability. For executive summaries or comparisons where small changes matter, one or two decimal places may communicate more. Choose precision consistently across comparable figures; varying decimal places from row to row can make values harder to compare.
Formatting negatives, zero, and text
A basic custom format applies the same general pattern to positive and negative numbers, which can make a negative amount appear with a minus sign, such as -$1.2M. Excel formats can also define separate sections for positive numbers, negative numbers, zero, and text. Sections are separated by semicolons, in that order.
For example, this format displays positive amounts normally, negative amounts in parentheses, zero as a dash, and text unchanged: "$"#,##0.0,,"M";("$"#,##0.0,,"M");"-";@. The third section affects numeric zero; the final @ is the text placeholder. This can be helpful in financial statements where a dash is preferred to $0.0M and parentheses are used to indicate negative amounts.
Apply a format with separate sections to a small sample first, especially if the workbook uses unusual negative-value conventions. Conditional formatting, accounting formats, or existing custom formats may also affect how the final presentation appears.
Formatting millions versus changing the value
Custom formatting is not the same as dividing the data by one million. With a display format, the cell retains the original amount, so a total of formatted cells still calculates from the full values. If A1 contains 1,250,000 and is shown as $1.3M, a calculation that refers to A1 still uses 1,250,000—not 1.3.
That difference is important when sharing data. Someone reading the displayed cell may see a shortened number, but copying the cell into another workbook generally copies its underlying value as well. Pasting as values can also preserve the full number while dropping its custom appearance, depending on the paste option selected.
Sometimes a report genuinely needs values converted into millions—for example, a separate table whose entries are defined in millions. In that case, create converted values in a separate range and label the units clearly. Do not divide the source data merely to make it look smaller if formulas, exports, or other users still rely on the full amounts.
Common problems and fixes
- The cells show the format code or a long number: Confirm that the cells are numeric rather than text, and that you entered the code in Format Cells > Number > Custom > Type. If the value is stored as text, applying a number format will not scale it.
- The result is thousands instead of millions: Check that the code has two scaling commas after the number placeholders. One trailing comma scales by thousands; two scale by millions.
- The dollar sign or M is missing: Use the quoted literals exactly as shown:
"$"and"M". Confirm that the cell is using the intended custom format rather than another number format. - A number near one million displays as $1.0M: This is rounding for display. Increase the number of decimal places or show the full amount if the distinction matters.
- The number appears as ####: The column may be too narrow to show the formatted result. Widen it or use Home > Format > AutoFit Column Width.
- Different computers show different currency symbols: A literal dollar sign displays as a dollar sign, but currency conventions and separators can vary across workbooks and regional settings. Check the displayed result in the workbook’s target locale.
When to use a chart axis or a formula instead
A cell format is often the simplest choice for a table. For a chart, you may want the source values to stay unchanged while the axis labels use a compact scale. Select the chart axis, open Format Axis, and inspect its Number options; available controls can vary by chart type and Excel build. You can apply a number format there, and if the chart is linked to source cells, the option to link the format to the source may affect the labels.
A formula that converts a number into a text label is another option, but it creates text rather than a numeric value. Text labels may not behave like numbers in later calculations or charting. Use a text formula only when the output is intended as a label, not as a replacement for the numeric data. For most worksheets, retain numeric values and use a custom number format.
Frequently Asked Questions
What Excel format shows dollars in millions?
Use "$"#,##0.0,,"M" for one decimal place, such as $1.3M. Use "$"#,##0,,"M" to omit decimals, or add another zero after the decimal point for two places.
Does this format divide the cell value by one million?
No. It scales the displayed number only. The original value remains intact for calculations, references, and most data operations.
How do I display negative millions in parentheses?
Use a separate negative-number section, such as "$"#,##0.0,,"M";("$"#,##0.0,,"M"). The semicolon separates the positive and negative display rules.
Can I show thousands as K and millions as M automatically?
A single simple format applies one scale consistently; it does not automatically switch between K and M at a chosen threshold. Use separate ranges or a more carefully designed conditional approach if the same column must use different units by magnitude. Keep the unit convention clear for readers.
Why does 999,999 display as $1.0M?
One decimal place rounds the displayed result to the nearest tenth of a million. The underlying amount remains 999,999. Increase the displayed precision or show full amounts when rounding could affect interpretation.
Choosing a format that readers can trust
For most reports, the one-decimal format "$"#,##0.0,,"M" is a readable starting point. Use the same precision throughout a table, make negative values easy to identify, and label columns or report sections so readers know amounts are shown in millions. Before distributing a workbook, check a small, representative set of positive, negative, zero, and near-boundary values. That quick review helps ensure the compact display remains clear without obscuring what the numbers actually mean.
Related
- Company Contact List Template for Excel
- Combine Excel Files into One: Methods and Best Practices
- How to Combine Excel Files: Methods and Common Issues
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Analysis Skills: What They Include and WhyExcel data analysis is the skill of turning raw spreadsheet data into reliable summaries, comparisons, and decisions. It…
- Does Excel Have a Debt Payoff Template? What to KnowYes. Excel can provide debt payoff templates through its template gallery or online template sources, though one may…
- Excel 3D Scatter Plot: Options and WorkaroundsExcel for Microsoft 365 does not include a native general-purpose 3D scatter chart with X, Y, and Z…
- Excel Horizontal Box Plot: How to Create and Format OneExcel for Microsoft 365 on Windows does not offer a native horizontal Box & Whisker chart: its built-in…
- Share Outlook Calendar URL: Links and AccessTo share an Outlook calendar URL, open Outlook on the web, go to Settings > Calendar > Shared…
- How to Add Footnotes in Word at the Bottom of a PageTo add a footnote at the bottom of a page in Microsoft Word, place the cursor where you…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.