How to Apply Conditional Formatting for Duplicate Days Off Without Coverage
Question details
The user needs a method to automatically highlight rows when multiple employees request the same day off but no shift coverage is recorded, and have the highlight disappear once coverage is entered.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Managing employee schedules to ensure adequate coverage on requested days off using automated cell highlighting.
- Observed behavior
- Users require a dynamic formula rule that checks both for duplicate dates in a specific column and for empty values in coverage columns, applying and removing row highlights accordingly.
Ensure your schedule data is organized with clear columns for the requested days off and the designated coverage assignments. Identify the exact cell ranges of your dataset before applying the formula to prevent reference errors.
Apply a Combined AND and COUNTIFS Formula Rule
Use a custom conditional formatting formula to check for duplicate days off and empty coverage cells simultaneously.
This method utilizes the COUNTIFS function to count how many times a specific date appears where the corresponding coverage column is blank. By wrapping it in an AND function, the rule ensures that a row is only highlighted if the count of uncovered duplicates is greater than 1, and the current row's coverage cells are also empty.
Highlight the entire data range you want to format. For example, select A4:E100, ensuring row 4 is the active cell.
Navigate to the Home tab on the ribbon, click on Conditional Formatting, and select New Rule from the dropdown menu.
Choose 'Use a formula to determine which cells to format' and enter: =AND(COUNTIFS($B$4:$B$100,$B4,$D$4:$D$100,"")>1,$D4="",$E4=""). This assumes column B contains the day off, and columns D and E contain coverage data.
Click the Format button, go to the Fill tab, choose a highlight color (e.g., light red), and click OK to apply the rule.
Easily Manage Employee Schedules with WPS Spreadsheet
WPS Spreadsheet provides powerful conditional formatting features and advanced functions like COUNTIFS to help you manage complex employee schedules and coverage tracking effortlessly.
- 1. Open Your Schedule: Launch WPS Spreadsheet and open your employee schedule workbook.
- 2. Access Conditional Formatting: Highlight your table data range, then navigate to Home > Conditional Formatting > New Rule.
- 3. Apply Custom Formula: Select the formula option, enter your COUNTIFS formula, and set your desired row highlight color.
- 4. Save and Share: Save your document securely. It remains perfectly formatted and fully compatible for colleagues using Excel.

Frequently Asked Questions
Why is my conditional formatting formula highlighting the wrong rows?
This usually happens if the active cell selected when creating the rule doesn't match the relative cell references in your formula. Ensure that if your formula references row 4 (e.g., $B4), you start highlighting your selection from row 4 down to the end of your dataset before creating the rule.
How do I adjust the formula if my coverage is only in one column?
If coverage is only recorded in column D, you can simplify the formula by removing the column E check. The formula would be: =AND(COUNTIFS($B$4:$B$100,$B4,$D$4:$D$100,"")>1,$D4="").
Can I highlight the entire row instead of just one cell?
Yes. To highlight the entire row, you must select the entire table range (e.g., A4:F100) before applying the rule, and ensure you lock the column letters in your formula using the dollar sign (like $B4 or $D4) so the format applies across the row.




