How to Highlight Employees Already Assigned in a WPS Office Spreadsheet Schedule
Question details
The user needs to automatically mark employees as unavailable when the same name appears in a scheduled-shifts section and restore the available formatting when the assignment is removed.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user manages a staff or shift schedule and wants to visually identify available employees who are already scheduled without manually checking each name.
- Observed behavior
- Names can appear in both the scheduled and available lists, but the available list does not automatically highlight assigned employees or revert formatting when a name is removed.
Open the schedule workbook in WPS Office Spreadsheets and confirm which range holds the scheduled names, for example B2:B10, and which range holds the available-employee list, for example B12:B30. Keep these ranges consistent before creating the conditional formatting rules.
Use Conditional Formatting with COUNTIF to Highlight Assigned Employees
Use this solution when you want the available list to update automatically as scheduled assignments change.
The COUNTIF function counts how many times each available employee name appears in the scheduled-shifts range. If the count is greater than 0, the employee is already assigned and can be highlighted as unavailable; if the count is 0, the employee remains available.
In WPS Office Spreadsheets, open your schedule workbook and select the available-employee range, for example B12:B30.
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($B$2:$B$10,B12)>0, then click Format to choose a red fill or another unavailable style.
Click OK to save the rule. Employees in the available list who also appear in B2:B10 are now highlighted automatically.
Create a second rule with =COUNTIF($B$2:$B$10,B12)=0 and choose green formatting, then click OK. When a scheduled name is added or removed, the available list updates immediately.
Highlight Scheduled Employees Automatically in WPS Office Spreadsheets
WPS Office Spreadsheets can open your existing Excel schedule and apply COUNTIF-based conditional formatting so assigned employees are marked automatically and formatting reverts when assignments are removed.
- 1. Install WPS Office: Download WPS Office from the official website, install it, and open your existing Excel schedule file.
- 2. Select the available range: In WPS Office Spreadsheets, select the available-employee cells, such as B12:B30.
- 3. Create the assigned rule: Click Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, and enter =COUNTIF($B$2:$B$10,B12)>0.
- 4. Create the available rule: Add a second rule with =COUNTIF($B$2:$B$10,B12)=0 and set green formatting for available employees.
- 5. Save and test: Click OK, then add or remove a name in the scheduled range to confirm the highlight updates automatically.
Frequently Asked Questions
Why is my conditional formatting highlighting the wrong employee?
Check that the scheduled range is absolute, such as $B$2:$B$10, and the first available cell reference is relative, such as B12. If your actual schedule uses different cells, adjust both ranges to match your worksheet.
Can I use this COUNTIF rule when the schedule is on another worksheet?
Yes. In WPS Office Spreadsheets, use a sheet reference in the formula, for example =COUNTIF(Schedule!$B$2:$B$10,B12)>0. Keep the available-cell reference relative to the first cell in the selected available list.
How do I make the highlight disappear when an employee is removed from the schedule?
The COUNTIF rule returns 0 when the name is no longer in the scheduled range, so the conditional formatting turns off automatically. If it does not, verify that no conflicting rules are applied and that the rule references the correct scheduled range.
Does WPS Office support Excel conditional formatting?
Yes. WPS Office Spreadsheets supports conditional formatting and can open, edit, and save .xlsx files, so you can apply these rules to your existing Excel schedule.
