Fix Excel Custom Number Formats for Millions and Thousands
Question details
The user needs an Excel custom number format that displays millions with M and thousands with K.

- 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.
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.
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.
In Excel, select the cells you want to format, then open Format Cells from the right-click menu.
On the Number tab, select Custom and find the Type field.
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.
Click OK and check that 1,000,000 displays as 1M and 100,000 displays as 100K.

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. Open your workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 2. Select the cells: Select the numeric cells, right-click, and choose Format Cells.
- 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. Apply and verify: Click OK and check that 1,000,000 displays as 1M and 100,000 displays as 100K.

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.




