Use Dynamic Conditional Formatting for Test Scores in WPS Spreadsheet
Question details
Create a small number of formula-based conditional formatting rules that evaluate an entire test-score range against minimum and maximum values on another worksheet.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user wants each score in a large range, such as B2:ZZ1000, to use the limits associated with its test column without creating separate formatting rules for every test.
- Observed behavior
- Scores should display different colors when they are below the minimum, above the maximum, or within the passing range defined for each test.
Set up a limits worksheet with one row per test, including the test name, minimum score, and maximum score. Make sure the test names in the score sheet header row exactly match the names in the limits table.
Apply three XLOOKUP-based rules to the complete score range
Use this method when each test column has its own minimum and maximum score stored in a lookup table on another worksheet.
For example, assume score headers are in row 1, scores begin in B2, and the Limits worksheet stores test names in A2:A100, minimum scores in B2:B100, and maximum scores in C2:C100. Apply each rule to the same full range, such as $B$2:$ZZ$1000.
In WPS Spreadsheet, select the complete score area, such as B2:ZZ1000, then choose Home > Conditional Formatting > New Rule.
Choose Use a formula to determine which cells to format, enter =AND(ISNUMBER(B2),B2<XLOOKUP(B$1,Limits!$A$2:$A$100,Limits!$B$2:$B$100)), select a red fill, and confirm that the Applies to range is $B$2:$ZZ$1000.
Choose Home > Conditional Formatting > New Rule again, enter =AND(ISNUMBER(B2),B2>XLOOKUP(B$1,Limits!$A$2:$A$100,Limits!$C$2:$C$100)), choose a yellow or orange fill, and apply it to $B$2:$ZZ$1000.
Add one more formula rule using =AND(ISNUMBER(B2),B2>=XLOOKUP(B$1,Limits!$A$2:$A$100,Limits!$B$2:$B$100),B2<=XLOOKUP(B$1,Limits!$A$2:$A$100,Limits!$C$2:$C$100)), then choose a green fill and apply it to the same range.
Open Home > Conditional Formatting > Manage Rules to verify that all three rules use $B$2:$ZZ$1000. Change a sample score or a limit value to confirm that the formatting updates automatically.
Highlight Test Scores Automatically with WPS Spreadsheet
WPS Spreadsheet lets you create formula-based conditional formatting rules for a large score range. Use XLOOKUP formulas to retrieve each test's limits from a separate worksheet, then automatically color scores that are below range, above range, or passing.
- 1. Create a limits table: In a WPS Spreadsheet worksheet, list each test name with its minimum and maximum score in adjacent columns.
- 2. Select the score matrix: Select the score cells, for example B2:ZZ1000, without including the test-name header row.
- 3. Add formula-based formatting: Go to Home > Conditional Formatting > New Rule and choose the formula rule option.
- 4. Use XLOOKUP formulas: Reference the current column header with B$1 and retrieve the corresponding limits from the limits worksheet with XLOOKUP.
- 5. Manage and verify rules: Use Home > Conditional Formatting > Manage Rules to check the shared Applies to range and adjust colors or formulas as needed.
Frequently Asked Questions
How do I apply one conditional formatting rule to every test score column?
Select the entire score range first, such as B2:ZZ1000, then create a formula-based rule. Use relative references like B2 for the current score and B$1 for the current test header so the formula adjusts across columns and rows.
Where should I store the minimum and maximum score limits?
Store them on a separate worksheet such as Limits. Put the test name in one column, the minimum score in the next column, and the maximum score in the third column, then reference those columns in XLOOKUP.
Why does the conditional formatting formula use B$1 instead of B1?
B$1 locks the header row while allowing the column to change as the rule moves across the selected score range. Each test column can therefore look up limits using its own header.
How can I prevent blank score cells from being colored?
Add ISNUMBER(B2) to each formula, such as =AND(ISNUMBER(B2),B2<your_lookup_formula). This limits the formatting to cells that contain numeric scores.
What happens when I change a minimum or maximum score in the limits table?
The conditional formatting updates automatically because the formulas retrieve the current values from the limits worksheet. No separate rule is needed for each test column.
