Fix Excel Custom Number Format Spaces That Do Not Scale Values by 1,000
Question details
The user needs Excel custom number formats to scale displayed values to thousands or millions again when a space is used as the thousands separator.
- Product
- Microsoft Excel version 2312
- Device & OS
- Windows
- Scenario
- The user applies a custom number format such as # ##0 "K€" to display a numeric value in thousands.
- Observed behavior
- A regular space in the custom format no longer scales the displayed value as expected after an Excel update.
Check the thousands separator currently used by Excel in a worksheet before editing the format. The separator character must match the character configured in Windows regional settings, which may be a narrow no-break space instead of a regular space.
Use the Windows-configured thousands separator in the custom format
Use this solution when a custom Excel format that formerly scaled values with spaces no longer works after an update.
Excel custom number formats can depend on the active Windows regional separator. In some regional configurations, Excel expects a narrow no-break space (Unicode U+202F) rather than a standard space (Unicode U+0020).
Press Windows+R, type Intl.cpl, and select OK to open the Region settings.
Open the Formats tab, select Additional settings, and review the Digit grouping symbol or thousands separator used by your regional format.
In Excel, select a cell with a number that already shows digit grouping, copy the separator character if possible, and paste that exact character into the Custom number format field.
Select the target cells, press Ctrl+1, open Number > Custom, then enter a format using the matching separator character, such as # ##0 "K€", and select OK.
Use comma scaling when your format does not require a space-based separator
Use this approach if you only need to display values in thousands or millions and can use Excel's standard trailing-comma scaling.
Create readable scaled number formats with WPS Spreadsheet
If an Excel update or regional-format change disrupts your workflow, WPS Office provides a free, lightweight spreadsheet alternative with a familiar interface and support for common Excel workbook formats. You can open existing files and continue formatting financial values with custom number displays after a seamless migration.
Frequently Asked Questions
Why does a regular space no longer work in my Excel custom number format?
Your Windows regional settings may use a narrow no-break space (U+202F) as the digit grouping symbol. Excel can require that exact character instead of a regular space (U+0020).
How do I find the thousands separator configured in Windows?
Press Windows+R, enter Intl.cpl, select OK, then open Formats > Additional settings and check the Digit grouping symbol or thousands separator.
How can I scale an Excel number to thousands without changing its value?
Use a custom format with one trailing comma after the numeric placeholders, such as #,##0,"K€". The comma divides only the displayed result by 1,000.
What custom format displays a value in millions in Excel?
Use two trailing commas, for example #,##0,,"M€". Excel displays the value divided by 1,000,000 while retaining the original number in the cell.
Will changing a custom number format alter formulas or stored values?
No. A custom number format changes only how the value is displayed. Formulas, calculations, and the underlying stored number remain unchanged.
