How to Create a Custom Excel Number Format for Thousands and Negative Values
Question details
The user needs to set up a custom number format that abbreviates numbers into thousands using a space as a thousands separator, properly retains the minus sign for negative numbers, and explicitly displays zeroes as '0'.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Formatting large numbers in financial, statistical, or data reports to improve readability by standardizing the display of thousands, negatives, and zeroes.
- Observed behavior
- The goal state is to apply a format where a number like -5,025,934 displays cleanly as -5 026, while 0 displays as 0, without breaking formatting rules for other standard values.
Ensure you have selected the specific cells or data ranges you want to format before applying the custom number string.
Apply a Conditional Custom Format Code
Use a specialized multi-part format code to handle values between -1,000,000,000 and 1,000,000,000 by dividing them into thousands and adding space separators.
Spreadsheet custom format codes allow you to dictate how positive numbers, negative numbers, zeroes, and text are displayed. By using commas at the end of the formatting rule, the software scales the display value down by a thousand.
Keep in mind that standard custom format fields have a limit on how many conditions they can store. Because of this, the provided solution works perfectly for values within the -1 billion to 1 billion range, but cannot handle scaling patterns beyond that limit in a single format rule.
Highlight the cells or columns containing the large numbers you want to format.
Right-click the selected area and choose "Format Cells...", or press the shortcut Ctrl+1 on your keyboard.
In the Format Cells dialog box, go to the "Number" tab and select "Custom" from the category list on the left.
In the "Type" input box, clear any existing text and paste the following code: [<=-1000000]-0 000,;[>=1000000]0 000,;0,
Click "OK" to apply the formatting. A value like -5,025,934 will now display as -5 026, and zero will show as 0.
Format Large Numbers Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced custom number format syntax. You can easily abbreviate large figures to thousands or millions, customize separators, and structure data reports exactly how you need them with excellent performance.
- 1. Open your spreadsheet: Launch WPS Spreadsheet and open the document containing your financial or statistical data.
- 2. Select the target cells: Highlight the range of data you wish to format, then press Ctrl+1 to open the Format Cells dialog.
- 3. Apply the custom format: Select the 'Custom' category and paste the exact format code: [<=-1000000]-0 000,;[>=1000000]0 000,;0, to finish.
Frequently Asked Questions
What does the comma at the end of a custom format code do?
A comma placed directly at the end of a number format string acts as a scaling thousands separator. It divides the displayed value by 1,000. For instance, the format '0,' will display the number 5,000 as 5.
How do I format numbers into millions using custom formatting?
To display numbers in millions, you simply use two commas at the end of your format string. For example, the custom format '0,,' will divide the value by one million, displaying 5,000,000 as 5.
Can I use color codes in this custom number format?
Yes. You can add color parameters enclosed in square brackets at the beginning of any section in the format code. For example, adding [Red] before the negative condition (e.g., [Red][<=-1000000]-0 000,) will make negative thousands display in red text.
