How to Automatically Format an Excel Risk Matrix with Conditional Formatting in WPS Office
Question details
The user needs to build a risk matrix that uses severity and probability levels, displays risk categories A through E, and automatically applies a dark green-to-red color scheme to indicate low to high risk.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is creating an Excel risk matrix and wants the risk categories or numeric risk scores to be colored automatically as data changes.
- Observed behavior
- The risk matrix currently shows severity and probability values or category labels, but the cell colors are not applied automatically by formulas.
Prepare a risk matrix with severity labels in rows and probability labels in columns, and decide whether each cell will contain a numeric risk score or a category letter A through E. Open the file in WPS Spreadsheets and make sure the Home tab is available.
Apply a Color Scale to Numeric Risk Scores
Use this method when each risk matrix cell contains a calculated numeric risk score, such as severity multiplied by probability.
A conditional formatting color scale automatically maps the lowest numeric scores to green and the highest scores to red, with a midpoint color if selected. This is the fastest method because it updates instantly when scores change.
Click and drag to select the matrix cells that contain numeric risk scores.
Go to the Home tab, click Conditional Formatting, then choose Color Scales.
Select the Green - Yellow - Red Color Scale. To use dark green for the minimum and red for the maximum, click More Rules, set the Minimum color to dark green, the Maximum color to red, and confirm with OK.
Create Five Conditional Formatting Rules for Category Letters A Through E
Use this method when the risk matrix displays category labels A to E instead of numeric scores and each category needs a specific color.
Automatically Format a Risk Matrix in WPS Office Spreadsheets
Create a live risk matrix in WPS Office Spreadsheets by using conditional formatting on numeric risk scores. WPS applies the same cell formatting behavior as Microsoft Excel, so your matrix can update colors automatically as severity and probability change.
- 1. Open or create the risk matrix: Launch WPS Office Spreadsheets and open your Excel risk matrix file, or create a new sheet with severity and probability labels.
- 2. Add a numeric risk score formula: In each matrix cell, enter a formula such as =severity_value*probability_value to calculate the risk score.
- 3. Select the numeric score range: Click and drag to select the formula cells that contain the calculated risk scores.
- 4. Apply a conditional formatting color scale: Go to Home, click Conditional Formatting, choose Color Scales, and select the Green - Yellow - Red Color Scale.
- 5. Review the automatic color result: Change a severity or probability value and confirm that the associated risk cell changes color automatically.
Frequently Asked Questions
Can I format an Excel risk matrix automatically without VBA in WPS?
Yes. Use WPS Spreadsheets conditional formatting. Select your risk cells, then go to Home > Conditional Formatting and choose Color Scales or create text rules for categories A through E.
How do I calculate a numeric risk score from severity and probability?
Use a formula in the risk cell, such as =B2*C2 where B2 is the severity value and C2 is the probability value. Then apply a color scale to the numeric score range.
Why do my A-E category cells not change color when I use a formula?
Cell formulas cannot apply colors. You need to add conditional formatting rules for each category letter, such as Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
Can I use dark green and red instead of the default green and red?
Yes. After choosing a color scale, open Conditional Formatting, click Manage Rules, edit the rule, and set the Minimum color to dark green and the Maximum color to red.
