Apply More Than Three Conditional Formatting Colors in WPS Office Excel
Question details
The user needs to apply more than three fill colors to a percentage range by creating separate formula-based conditional formatting rules instead of relying on the built-in two- or three-color scale.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user wants to visually distinguish six percentage categories in a worksheet with a different fill color for each range.
- Observed behavior
- Excel color scales normally provide only two- or three-color gradients, so six distinct percentage categories require separate formula-based conditional formatting rules.
Make sure the percentage values are stored as numbers or percentages in a single column or row. Note the first cell in the selected range because each formula must use that reference.
Create a Separate Formula-Based Rule for Each Percentage Category
Use this method when you need six or more fill colors because the built-in two- or three-color scale cannot highlight each percentage band distinctly.
The formulas evaluate each cell against the first row reference in the selected range. You can adjust the thresholds and cell reference to match your data.
Open your workbook in WPS Office Spreadsheet and select the cells that contain the percentage values you want to format.
Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.
Enter =AND($A1>=0.95,$A1<=1), click Format, choose a fill color on the Fill tab, and confirm with OK.
Create rules using =AND($A1>=0.9,$A1<0.95), =AND($A1>=0.85,$A1<0.9), =AND($A1>=0.8,$A1<0.85), =AND($A1>=0.75,$A1<0.8), and =$A1<0.75, assigning a different fill color to each rule.
Click OK on each rule, return to the worksheet, and confirm that each percentage band shows its assigned color.
Apply Multiple Conditional Formatting Rule Colors in WPS Office Spreadsheet
You can create formula-based conditional formatting rules in WPS Office Spreadsheet to apply a different fill color for each percentage band, giving you more than the built-in two- or three-color scale limit.
- 1. Open your workbook in WPS Office Spreadsheet: Launch WPS Office, open the workbook that contains the percentage values, and go to the worksheet you want to format.
- 2. Select the target cells: Select the range that contains the values, such as A1:A100.
- 3. Add a formula-based conditional formatting rule: Click Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, and enter the first category formula.
- 4. Assign a unique fill color and repeat: Click Format, choose a fill color, confirm, and repeat for each percentage range until all six categories have a distinct color.
- 5. Save as .xlsx: Save the workbook as a .xlsx file to keep every conditional formatting rule compatible with Microsoft Excel.
Frequently Asked Questions
Why can’t a three-color scale show six percentage categories?
Color scales are designed for two- or three-color gradients. To highlight six distinct ranges, use separate formula-based rules under Home > Conditional Formatting > New Rule.
Do I have to use $A1 in every formula?
Use the first cell in your selected range so WPS Office Spreadsheet applies the rule relative to each row or column correctly. If your range starts at a different cell, adjust the reference accordingly.
Can I use percentages such as 95% instead of 0.95?
Because percentage-formatted cells store values as decimal numbers, use numeric thresholds like 0.95 for 95%. You can also reference cells containing the threshold values.
How do I add more than six colors later?
Open Home > Conditional Formatting > Manage Rules, create another formula rule for the new range, assign a different fill color, and check the rule order.
Will these conditional formatting rules work in Microsoft Excel?
Yes. Formula-based conditional formatting rules are saved in .xlsx format, so they remain visible and editable when you open the file in Microsoft Excel.
