Apply Roster Colors from a Legend in WPS Spreadsheets Using Conditional Formatting
Question details
The user needs to apply fill colors to attendance roster cells based on shift and leave codes such as D, SL, and T, using a separate legend that maps each code to a color.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Formatting a staff attendance roster so each cell automatically changes color according to the attendance code entered.
- Observed behavior
- Cells should display the matching legend color for each code while keeping the roster values unchanged.
Make sure the roster and the legend are in the same workbook, and identify the top-left cell of the roster range, for example A2, and the legend range, for example Legend!A2:B10.
Create a VLOOKUP-based conditional formatting rule for each roster code
Use this when the legend may change or when you want each attendance code to inherit the exact color mapped in a separate legend range.
WPS Spreadsheets evaluates conditional formatting formulas relative to the active cell. When you select the full roster, use the top-left cell such as A2 in the formula so the rule adjusts correctly for every selected cell.
Open your attendance workbook in WPS Spreadsheets and select the entire roster area where codes will be entered, for example A2:G50.
Go to the Home tab and click Conditional Formatting > New Rule.
In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
In the formula box, enter =VLOOKUP(A2,Legend!$A$2:$B$10,2,FALSE)="D". Replace A2 with the top-left cell of your selection and Legend!$A$2:$B$10 with your actual legend range.
Click Format, open the Fill or Background tab, choose the color that matches the legend for code D, and confirm with OK.
Create another rule for each additional code, such as SL and T, by changing the final text in the formula to "SL" or "T" and assigning the corresponding color.
Use built-in highlight rules for fixed attendance codes
Quick option when you have only a few fixed codes and do not need colors to follow a separate legend.
Color Roster Cells from a Legend with WPS Spreadsheets Conditional Formatting
Use WPS Spreadsheets to create formula-based conditional formatting rules that read attendance codes from your roster and apply the matching legend color automatically. You can set up D, SL, and T rules in a few steps, and the workbook stays compatible with Excel.
- 1. Download and open WPS Office: Download WPS Office for your device, install it, and open your attendance roster workbook in WPS Spreadsheets.
- 2. Select the roster area: Highlight the range where attendance codes will be entered, for example A2:G50.
- 3. Create a legend-driven rule: On the Home tab, select Conditional Formatting > New Rule > Use a formula to determine which cells to format.
- 4. Enter and format the code formula: Use =VLOOKUP(A2,Legend!$A$2:$B$10,2,FALSE)="D" and choose the legend color for D, then repeat for SL and T.
- 5. Save the workbook: Save the file as .xlsx to keep the conditional formatting rules and maintain Excel compatibility.
Frequently Asked Questions
Can the legend be stored on a different sheet?
Yes. Reference the sheet in the rule formula, such as Legend!$A$2:$B$10. WPS Spreadsheets will read the legend values from that sheet.
Why use A2 in the formula if my roster contains many columns?
When you select the whole roster, the conditional formatting formula is relative to the top-left cell of the selection. Use that cell reference so WPS adjusts the lookup correctly across rows and columns.
How do I stop the roster values from being replaced by colors?
Conditional formatting only changes the cell appearance, not the cell value. The original attendance code remains in the cell.
How can I change a legend color later?
Open Home > Conditional Formatting > Manage Rules, select the rule for that code, click Edit Rule, and change the format. If the legend mapping changes, update the legend range or formula reference.
