Formatting Issues

How to Apply Conditional Formatting for Duplicate Days Off Without Coverage

Elise WilliamsElise Williams Nov 10, 2026 870 views

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.

How to Apply Conditional Formatting for Duplicate Days Off Without Coverage
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the Data Range

Highlight the entire data range you want to format. For example, select A4:E100, ensuring row 4 is the active cell.

2
Open Conditional Formatting

Navigate to the Home tab on the ribbon, click on Conditional Formatting, and select New Rule from the dropdown menu.

3
Enter the Formula

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.

4
Set the Highlight Format

Click the Format button, go to the Fill tab, choose a highlight color (e.g., light red), and click OK to apply the rule.

Dynamic Formatting: Once the rule is applied, the highlight will automatically disappear as soon as you type a name or value into the coverage columns (D or E) for those specific rows.
Free Spreadsheet Software

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. 1. Open Your Schedule: Launch WPS Spreadsheet and open your employee schedule workbook.
  2. 2. Access Conditional Formatting: Highlight your table data range, then navigate to Home > Conditional Formatting > New Rule.
  3. 3. Apply Custom Formula: Select the formula option, enter your COUNTIFS formula, and set your desired row highlight color.
  4. 4. Save and Share: Save your document securely. It remains perfectly formatted and fully compatible for colleagues using Excel.
Fully compatible with Microsoft Excel (.xlsx) formatsAdvanced conditional formatting with custom formula supportLightweight application with fast processing speed for large datasetsFree built-in schedule management templates
microsoft office alternative - wps office

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.