How to Highlight Employees Assigned to a Crew in WPS Spreadsheets
Question details
The user needs to automatically highlight employee names in a master list when those names are entered into a crew assignment range, so assigned employees can be identified at a glance.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Building a crew assignment checklist from an employee roster and a crew assignment area.
- Observed behavior
- The employee list does not currently show which employees have already been assigned to a crew.
Confirm that employee names are entered consistently in both the employee list and the crew assignment range. Select the employee list range, such as G2:G50, before creating the conditional formatting rule.
Highlight Assigned Employees with a COUNTIF Conditional Formatting Rule
Use this method to automatically shade each employee in the master list as soon as their name appears in the crew assignment range.
The formula counts how many times the name in the current employee cell appears in the crew assignment range. The mixed reference $G2 keeps the column fixed while allowing the rule to evaluate each row in the employee list.
Select the employee list range, such as G2:G50, in your worksheet.
Go to the Home tab, click Conditional Formatting, and choose New Rule.
In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
Enter =COUNTIF($A$2:$D$20,$G2)>0, replacing A2:D20 with your crew assignment range and G2 with the first selected employee cell.
Click Format, choose a fill color or font style, then confirm with OK to apply the highlighting.
Flag Crew Names That Do Not Exist in the Employee List
Use this check when you also want to catch misspelled or unknown names entered directly in the crew assignment range.
Use WPS Spreadsheets to Highlight Assigned Employees with Conditional Formatting
Create a formula-based conditional formatting rule in WPS Spreadsheets to automatically highlight employee names that appear in a crew assignment range. The same COUNTIF technique works in WPS Spreadsheets and keeps your workbook compatible with Microsoft Excel.
- 1. Open your crew workbook: Open the workbook that contains your employee list and crew assignment range in WPS Spreadsheets.
- 2. Select the employee list: Select the employee list range, such as G2:G50, where you want assigned employees highlighted.
- 3. Create the rule: On the Home tab, click Conditional Formatting > New Rule, and choose Use a formula to determine which cells to format.
- 4. Enter the formula: Type =COUNTIF($A$2:$D$20,$G2)>0 and adjust the ranges to match your crew assignment area and employee list.
- 5. Format and save: Choose a fill color, click OK, and save the workbook as .xlsx to keep the formatting when sharing with Excel users.
Frequently Asked Questions
Why is my COUNTIF rule not highlighting any employees?
Check that the employee list range starts with the same cell used in the formula reference, such as $G2. Also verify that names are spelled exactly the same in both the employee list and the crew range, and that the crew range in the formula matches the actual assignment area.
Can I use this conditional formatting rule when the crew range is on another sheet?
Yes. Include the sheet name in the COUNTIF reference, for example =COUNTIF(Crew!$A$2:$D$20,$G2)>0. In WPS Spreadsheets, you can also select the range on the other sheet while building the rule.
How do I highlight employees who have not been assigned yet instead?
Create a second formula-based rule on the employee list using =COUNTIF($A$2:$D$20,$G2)=0 and choose a different fill color or border. This visually separates unassigned employees from assigned ones.
Can the same rule highlight the entire employee row rather than just the name cell?
Yes. Select the entire employee table or row range first, then use a formula that still checks the name column, such as =COUNTIF($A$2:$D$20,$G2)>0 with the row reference adjusted to the first selected row.
