Formatting Issues

How to Create Red, Amber, and Green Status Conditional Formatting in WPS Office Excel

John Wilson Nov 13, 2026 870 views

Question details

The user needs to apply red, amber, and green status conditional formatting in Excel based on target percentage thresholds, count each status, and display an overall result that can be used in a Power BI card.

Product: WPS Office Excel
Device & OS: not provided
Scenario: Business reporting or dashboard preparation where metrics must be classified as Red, Amber, or Green against target percentages.
Observed behavior: Each metric should show a Red, Amber, or Green format based on its value, and a summary cell should show the overall status for use in a Power BI card.

Before you start

Open the workbook that contains the metric values and targets, and confirm whether the values are stored as percentages or whole numbers. Decide the boundaries—for example, Red below 40%, Amber 40% through 49%, and Green 50% or higher—before creating the rules.

Solution 1Recommended

Create Red, Amber, and Green Conditional Formatting Rules in WPS Excel

Use this recommended method when you want metric cells to automatically change color according to their value and your RAG thresholds.

WPS Office Excel supports the same rule-based conditional formatting as Microsoft Excel. The formulas below treat the active cell in the selected range as relative, so they update for each row automatically.

Select the metric value cells

In WPS Office Excel, open your worksheet and select the range that contains the metric results, such as B2:B100.

Open a new conditional formatting rule

On the Home tab, click Conditional Formatting, then choose New Rule, and select Use a formula to determine which cells to format.

Create the Red rule

In the formula box, enter =B2<40% (or =B2<0.4). Click Format, choose a red fill or font color, and click OK to save the rule.

Create the Amber rule

Click Conditional Formatting > New Rule > Use a formula to determine which cells to format again. Enter =AND(B2>=40%,B2<50%) and set an amber fill or font color.

Create the Green rule

Repeat the same path and enter =B2>=50%. Choose a green fill or font color, then click OK to return to the worksheet.

Rules applied to the entire range: The formulas use the first cell of the selected range (for example B2). WPS Office Excel adjusts the references for every other selected cell automatically.

Solution 2

Count Red, Amber, and Green Statuses with COUNTIF in WPS Excel

Use this method when you need a summary table or a single overall status that can feed a Power BI card.

Solution 3

Apply Conditional Formatting to the Overall Result Cell for Power BI Use

Use this method after the summary result is created to color the overall RAG status and make the dashboard output visually consistent.

WPS Office Excel Solution

Create Red, Amber, and Green Status Conditional Formatting and Summary Count in WPS Office Excel

Use WPS Office Excel to apply Red, Amber, and Green conditional formatting rules based on target percentages, count the statuses with COUNTIF, and show the overall result for a Power BI card—all in a familiar spreadsheet interface.

1. Open the workbook and select the metric range: Launch WPS Office Excel, open your .xlsx workbook, and select the cells that contain the metric values.
2. Add the RAG conditional formatting rules: Go to Home > Conditional Formatting > New Rule > Use a formula. Add Red with =B2<40%, Amber with =AND(B2>=40%,B2<50%), and Green with =B2>=50%, then choose matching fills.
3. Create the status and summary formulas: Add a Status column with =IF(B2<40%,"Red",IF(B2<50%,"Amber","Green")), fill it down, and count the statuses with COUNTIF.
4. Build the overall result: Enter a result formula such as =IF(GreenCount>=10,"Green",IF(GreenCount>7,"Amber","Red")) and apply matching conditional formatting to the result cell.
5. Use the result in Power BI: Save the workbook and connect it in Power BI via Get data > Excel workbook, then add the overall result to a card visual.

Automatic traffic-light RAG formatting based on your Red, Amber, and Green thresholdsCOUNTIF and IF formulas make status counts and the overall result update instantlyFull compatibility with Microsoft Excel .xlsx workbooks and formulasFamiliar Home tab and Conditional Formatting tools for an easy migrationFree, lightweight WPS Office spreadsheet experience for Excel-style reporting

microsoft office alternative - wps office

Frequently Asked Questions

Why does COUNTIF return 0 when my cells are colored red, amber, or green?

Conditional formatting changes only the displayed color, not the cell value. Add a helper Status column with an IF formula such as =IF(B2<40%,"Red",IF(B2<50%,"Amber","Green")), or use COUNTIFS on the numeric metric values, and then count the text or numeric thresholds.

How do I make Amber cover 40% to 49% without including 50%?

Use a conditional formatting rule based on a formula, and enter =AND(B2>=40%,B2<50%) for Amber. For Green, use =B2>=50%, and for Red, use =B2<40%.

Can I use this Red, Amber, Green summary in a Power BI card?

Yes. Create a summary cell that returns Green, Amber, or Red, save the workbook, and connect Power BI to the Excel file with Get data > Excel workbook. Then add the summary result field to a card visual.

Does WPS Office support the same conditional formatting formulas as Microsoft Excel?

Yes. Open the workbook in WPS Office Excel, select the range, and go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. The same formulas work in both applications.

Document & File Issues