Formatting Issues

Fix Excel Custom Number Formats for Millions and Thousands

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 870 views

Question details

The user needs an Excel custom number format that displays millions with M and thousands with K.

Fix Excel Custom Number Formats for Millions and Thousands
Product
Microsoft Excel
Device & OS
not provided
Scenario
Formatting numeric cells using a custom format that matches the configured regional separators.
Observed behavior
The existing format displays incorrect commas and text instead of the expected M and K suffixes.
Before you start

Check that the cells contain numbers rather than text. In Excel, check the configured thousands separator; it may come from your system's regional settings.

Solution 1Recommended

Apply a Custom Format That Matches Your Thousands Separator

Use this format to display positive values of one million or more with M and smaller values with K.

1
Select the numeric cells

In Excel, select the cells you want to format, then open Format Cells from the right-click menu.

2
Open the custom format editor

On the Number tab, select Custom and find the Type field.

3
Enter the matching format

If the thousands separator is a comma, enter [>999999]0,,"M";0,"K". If it is a period, try [>999999]0.."M";0."K" instead.

4
Apply and check the result

Click OK and check that 1,000,000 displays as 1M and 100,000 displays as 100K.

Apply a Custom Format That Matches Your Thousands Separator
Display rounding: The format rounds the displayed result to a whole M or K without changing the stored value. Values below one million, including zero and negative values, use the K section.
WPS Spreadsheet

Format Millions and Thousands in WPS Spreadsheet

Use a custom number format in WPS Spreadsheet to display numeric values with M or K while keeping the underlying numbers unchanged.

  1. 1. Open your workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Select the cells: Select the numeric cells, right-click, and choose Format Cells.
  3. 3. Enter the custom format: Select Custom on the Number tab. For a comma thousands separator, enter [>999999]0,,"M";0,"K" in the format field. If your separator is a period, try [>999999]0.."M";0."K".
  4. 4. Apply and verify: Click OK and check that 1,000,000 displays as 1M and 100,000 displays as 100K.
Display large values with M and K suffixesKeep underlying numeric values available for calculationsOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet formatting interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does my custom format show incorrect separators or text?

The format may use a separator that does not match your regional settings. In Format Cells > Custom, try the comma-based code if your thousands separator is a comma, or the period-based code if it is a period.

Does the custom format change the value used in calculations?

No. The format changes only how the number is displayed. Select a formatted cell to see its underlying value in the formula bar.

Why can a value below one million display as 1000K?

The K section rounds the displayed value to a whole number. For example, 999,999 can display as 1000K even though its stored value remains unchanged.

Will this format display negative millions with M?

No. The condition applies the M section only to values greater than 999,999. Negative values use the K section, so they need a different format if you want negative millions displayed with M.