Yes. In Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1, choose Custom, and enter #,##0.0,,"M". The two commas scale the displayed number to millions, while the quoted M adds a label; the underlying values stay unchanged.
Apply a custom format in Excel
Use a custom number format when you want large values to be easier to scan but still need Excel to calculate with the original numbers. For example, the format above displays 1,234,567 as 1.2M. To apply it:
- Select the cells or range you want to format. You can also select a whole column if new entries should use the same display.
- Press Ctrl+1 to open Format Cells. Alternatively, on the Home tab, use the Number group’s dialog box launcher.
- On the Number tab, select Custom in the Category list.
- Click in the Type box, enter
#,##0.0,,"M", and select OK.
On Excel for Mac, select the cells and press Command+1 to open Format Cells; then use the Custom category and Type box. Excel for the web can display many custom formats, but its options for creating or editing them may differ by build. If you cannot find the Custom type controls, open the workbook in the desktop app.
To show whole millions instead of one decimal place, use #,##0,,"M". For two decimal places, use #,##0.00,,"M". You can replace M with a longer label, such as #,##0.0,,"million", if the column has room.
How the commas and placeholders work
Excel uses commas in number formats for two different purposes. A comma between digit placeholders, as in #,##0, adds thousands grouping. A comma at the end of the numeric part of a format scales the displayed number down by a factor of 1,000. Two trailing scaling commas divide the display by 1,000,000.
In #,##0.0,,"M", the first part, #,##0.0, specifies the displayed digits and one decimal place. The two commas after the decimal-place placeholder apply the million-scale display. The quoted M is literal text, not a unit conversion. As a result, Excel displays 1,234,567 as 1.2M, but it does not replace the stored value with 1.2 million.
The # placeholder displays a digit when one is needed, while 0 requires a digit in that position. For instance, a format using 0.0,,"M" shows a leading zero for values below one million, such as 0.5M. That can be easier to read than a display that begins with a decimal point. Choose a pattern that makes small values clear in the context of your report.
Choose the right number of decimal places
Decimal places control rounding in the display, not the precision of the stored value. A one-decimal format is compact and often suitable for summaries. A two-decimal format gives more detail but can make a busy table harder to scan. A zero-decimal format is shortest, but it can hide differences that matter.
For example, 1,234,567 appears as 1.2M with one decimal place, 1.23M with two, and 1M with none. The one-decimal display rounds to the nearest tenth of a million, so 1,250,000 appears as 1.3M. Excel still retains the exact source number for formulas, sorting, and further calculations.
Consider the scale of the values before choosing a format. If a column contains 80,000, the one-decimal million format displays 0.1M, which is a rounded representation and may conceal how small the value is. For values below 50,000, one decimal place can even display 0.0M. Increase the precision, use a different unit, or show the full value if that rounding would mislead readers.
Currency, negatives, and zero values
To show currency in millions, add the appropriate currency symbol before the number pattern. For example, $#,##0.0,,"M" displays 1,234,567 as $1.2M. Use the symbol and conventions appropriate for the workbook’s audience; a dollar sign alone does not identify which country’s currency is meant.
Custom formats can use separate sections for positive numbers, negative numbers, and zero values, separated by semicolons. For example, $#,##0.0,,"M";[Red]($#,##0.0,,"M");$0.0,,"M" sets a positive pattern, a red parenthesized negative pattern, and a zero pattern, in that order. This can make a financial summary easier to interpret, but check the result with representative positive, negative, and zero values before applying it to a report.
If your workbook uses a different currency or accounting convention, adjust the pattern rather than assuming the dollar example is suitable. Number-format appearance can also depend on regional settings, especially decimal and thousands separators. The format changes presentation; it does not convert one currency into another.
What changes, and what does not
A custom number format changes how a value appears in a cell, not the value Excel stores. If A1 contains 1,234,567 and displays as 1.2M, a formula that adds A1 to another cell still uses 1,234,567. Sorting and filtering also use the underlying value, not the rounded text that appears on screen.
This distinction is useful in working spreadsheets: you can keep full precision for totals while making a summary column readable. It also explains why a formula bar or a cell reference may show a number that looks different from the formatted cell. The display is abbreviated; the data has not been shortened.
If you specifically need a scaled number in another cell, that is a calculation rather than a display format. With the original number in A2, enter =A2/1000000 in B2. If A2 is 1,234,567, B2 returns 1.234567. You could then format B2 to show one or two decimal places and label the adjacent heading Millions. This approach changes the value in the result cell and may affect later calculations, so use it only when a separate scaled value is wanted.
Common problems and how to fix them
- The value does not look scaled. Confirm that both scaling commas appear after the numeric placeholders and before the quoted suffix. A comma used for grouping is not the same as a trailing scaling comma.
- The suffix appears as part of a format code. Put literal letters in quotation marks, as in
"M". Without quotes, letters can have special meanings in some number formats. - The number shows too few significant digits. Add decimal places, or use a thousands format instead if the values are not consistently large enough to express clearly in millions.
- A cell shows #######. The column may be too narrow for the formatted result. Widen it or reduce the displayed decimal places; the hash marks do not usually mean the stored value is damaged.
- The displayed total seems slightly different from the visible entries. Excel calculates with the underlying values, including digits hidden by rounding. Increase decimal places temporarily or inspect the formula bar to understand the difference.
Also check that the cells contain numeric values, not numbers imported as text. A number format cannot scale text into a numeric display. If a format appears not to apply, test a known numeric cell and check whether the cell is stored as a number.
Fixed millions or automatic units?
A format such as #,##0.0,,"M" always scales by millions. It does not switch to thousands for smaller values or billions for larger ones. That consistency is helpful when every value in a table is intended to use the same unit, and it makes columns easier to compare.
If a dataset spans several orders of magnitude, a single fixed unit may not be the clearest choice. Consider using separate columns or sections with explicit units, or create a separate presentation field with a formula and a clearly labeled unit. Excel custom formats can also use conditional sections for ranges, but those formats are less obvious to other workbook users and should be tested carefully, particularly for negative values.
For charts, format the axis or data labels as needed rather than assuming that a cell’s appearance will always carry over exactly. Chart settings control how chart numbers are presented, and a chart may need its own number format or axis display units. Likewise, exporting a sheet to a plain-text file such as CSV does not preserve Excel’s custom cell formatting; the exported data may contain the full underlying numbers.
Frequently Asked Questions
How do I show millions without the M?
Use #,##0.0,, for one decimal place or #,##0,, for whole millions. The value is still scaled only for display, but without a suffix readers may not know the unit. Add a clear column heading such as Millions.
Can I show one decimal place and keep the full number for calculations?
Yes. Apply a custom format such as #,##0.0,,"M". Excel keeps the original value and uses it in calculations; only the visible display is rounded.
How do I display negative millions in parentheses?
Use separate positive and negative sections in a custom format, with a semicolon between them. For example, #,##0.0,,"M";(#,##0.0,,"M") displays negative values in parentheses. Add a third section after another semicolon if you want a special display for zero.
Can I type 1.2M into a cell and have Excel treat it as 1.2 million?
Do not assume that Excel will interpret the suffix M as a multiplier when you type it. Enter the numeric value, such as 1200000, and apply a display format, or use a clearly defined formula or data-entry process that converts the text to a number.
Does the custom format change the actual number?
No. It changes the way the number is displayed. If you need a genuinely scaled result in another cell, divide the source value by 1,000,000 and label the result so its unit is unambiguous.
Related
- Excel Power Query Jira API Key Name: What to Enter
- Excel Power Query: Merge All Files in a Folder
- Excel Power Query Merge Table Types and When to Use Them
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Can You Insert a Digital Signature in Word?Yes. In Word for Microsoft 365 on Windows, you can add a signature line and sign it with…
- Consolidate Excel Sheets with Power Query: A Practical GuideYes. In Excel for Microsoft 365 on Windows, use Power Query to bring sheets or files with similar…
- Most Used Excel Shortcut KeysThe most used Excel shortcut keys on Windows are Ctrl+C and Ctrl+V to copy and paste, Ctrl+Z to…
- Excel Data Model on Mac: What Works and What Doesn’tExcel for Mac does not include Power Pivot, so you cannot create, manage, or refresh a full Excel…
- Protect Sheet vs. Protect Workbook in Excel: Key DifferencesIn Excel, Protect Sheet limits what people can change on one worksheet, while Protect Workbook (in the Review…
- How to Set an Out of Office Reply in Outlook 365To set an out of office reply in Outlook 365, open Automatic Replies, switch them on, write your…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.