Formatting Issues

How to Display Excel Values in Millions with Custom Number Formats

Jason Miller Oct 23, 2026 868 views

Question details

The user wants to format large numerical values to visually display in millions without modifying the underlying numeric data stored in the cells.

How to Display Excel Values in Millions with Custom Number Formats

Product: Excel
Device & OS: not provided
Scenario: Working with large financial or statistical data sets where displaying full numbers (e.g., 1,000,000) makes the spreadsheet cluttered and difficult to read.
Observed behavior: The desired goal state is to display numbers scaled down to the nearest million (e.g., displaying 1 or 1 M) visually, while retaining the exact original number for accurate calculations.

Before you start

Ensure you have selected the specific cells or range of cells containing the large numerical values you intend to format before opening the settings.

Solution 1Recommended

Use Custom Number Formatting for Millions

Apply a custom number format string containing commas to visually scale your numbers down by a million.

Excel allows you to change how numbers look visually without changing their actual underlying value. In Excel custom formatting rules, adding a single comma at the end of a format string scales the number by thousands. Adding two commas consecutively will scale the display by millions.

Select the target cells

Highlight the range of cells containing the large numbers you want to format in millions.

Open the Format Cells dialog

Right-click on the selected cells and choose 'Format Cells' from the context menu, or press the Ctrl+1 shortcut on your keyboard.

Navigate to Custom formats

In the Format Cells dialog window, go to the 'Number' tab, and then select 'Custom' from the Category list on the left side.

Enter the custom format code

Click inside the 'Type' input box. To display 1,000,000 as 1, type 0,, and to add a descriptive 'M' suffix, type 0,," M".

Apply the format

Click 'OK' to apply the new formatting. Your large numbers will now be neatly displayed in millions.

Use Custom Number Formatting for Millions

Underlying Value Remains Intact: The format changes only the displayed visual value. The original number remains entirely unchanged in the formula bar, ensuring that all downstream calculations and formulas stay perfectly accurate.

Format Numbers Easily in WPS Spreadsheet

Format Large Numbers Instantly with WPS Spreadsheet

WPS Spreadsheet fully supports Excel's custom number formatting rules. You can seamlessly apply custom scaling, like displaying numbers in thousands or millions, to make financial reporting much cleaner without altering your core dataset.

1. Select your data: Open your workbook in WPS Spreadsheet and highlight the cells you wish to format.
2. Access Format Cells: Right-click the selection and click 'Format Cells' (or use the Ctrl+1 keyboard shortcut).
3. Apply the format: Click on 'Custom' under the Category list, type 0,," M" into the Type box, and click OK to finish.

Easily apply advanced custom number formatting for thousands and millionsFully compatible with Microsoft Excel custom formatting structures and files (.xlsx)Lightweight, fast, and features a familiar user interface for smooth data analysisFree to use core spreadsheet features for everyday personal and office tasks

microsoft office alternative - wps office

Frequently Asked Questions

How do I format values in thousands instead of millions in Excel?

To display numbers in thousands, use a single comma at the end of the custom format code. For example, enter `0,` to show 1,000 as 1, or type `0," K"` to display it as 1 K.

Will formatting as millions affect the formulas that reference these cells?

No. Custom number formatting only changes the visual display of the cell on your screen. Any formulas referencing these cells will still read and calculate using the full original number.

How can I include decimal places when displaying values in millions?

You can add a decimal point to the custom format code to preserve precision. For example, entering `0.00,,` will accurately display 1,500,000 as 1.50.

How do I format negative numbers and zeros properly along with positive millions?

You can define distinct formats for positive, negative, zero, and text values by separating them with semicolons. For instance, using the format code `0.00,," M";-0.00,," M";"-"` will format both positive and negative millions properly while displaying a simple dash for any zero values.

Document & File Issues