How to Display Negative Percentages in Red and Positive Percentages in Green in WPS Spreadsheets
Question details
The user wants percentage values to be visually distinguished by color: positive percentages in green, negative percentages in red, and optionally zero displayed as a dash.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Formatting a spreadsheet so percentage changes, growth rates, or variance values are easier to read at a glance.
- Observed behavior
- Percentages currently appear in a single color, making it difficult to quickly identify positive and negative values.
Select the cells that contain percentage values and confirm they are already formatted as percentages. Open the worksheet in WPS Spreadsheets so you can access the Format Cells dialog and conditional formatting tools.
Apply a custom number format for green positive and red negative percentages
Use this method to apply one consistent format directly to selected percentage cells without creating separate rules.
WPS Spreadsheets uses the same custom number format syntax as Microsoft Excel, so the color-coded format can be reused across both applications.
Highlight the range of cells containing percentage values, then press Ctrl+1 or right-click and choose Format Cells.
In the Format Cells dialog, click the Number tab, select Custom, and type [Green]0%;[Red]-0%;"-";@ in the Type box. Use [Color10] instead of [Green] if the green is too light.
Click OK. Positive percentages display in green, negative percentages display in red, and zero values display as a dash.
Create conditional formatting rules for positive and negative percentages
Use this approach when you want separate rules that can be edited, paused, or extended without changing the underlying number format.
Format percentage colors quickly with WPS Spreadsheets
WPS Spreadsheets can directly apply custom number formats and conditional formatting so positive percentages appear green and negative percentages appear red. It supports Excel-compatible .xlsx formatting, so you can create the same visual reports without changing tools.
- 1. Download and install WPS Office: Go to the official WPS Office download page and install WPS Spreadsheets on your Windows, macOS, Linux, iOS, or Android device.
- 2. Open your workbook: Launch WPS Spreadsheets and open the .xlsx or WPS workbook containing the percentage values.
- 3. Select and format the percentage cells: Highlight the target cells, press Ctrl+1, and apply the custom format [Green]0%;[Red]-0%;"-";@ from the Number tab.
- 4. Verify the colors: Review the sheet to confirm positive percentages are green, negative percentages are red, and zero values show a dash.
Frequently Asked Questions
What is the custom format to show positive percentages in green and negative percentages in red?
Use [Green]0%;[Red]-0%;"-";@ in the Custom category of the Format Cells dialog. If the green is too light, replace [Green] with [Color10].
How do I make zero percentage values appear as a dash?
The third section of the custom format controls zero values. In [Green]0%;[Red]-0%;"-";@, zero percentages display as a dash.
Can I use Conditional Formatting instead of a custom number format?
Yes. On the Home tab, click Conditional Formatting, then create a Greater Than rule for values above 0 with green formatting and a Less Than rule for values below 0 with red formatting.
Will this formatting work when opening the file in Microsoft Excel?
Yes. WPS Spreadsheets uses Excel-compatible custom number format syntax and conditional formatting, so the colors and dash formatting are preserved in .xlsx files.
How can I change the green color if it is too light?
Replace [Green] with [Color10] in the custom number format code to use a darker green that is easier to read on white backgrounds.
