How to Highlight Blank Cells After a Due Date in Excel
Question details
Set up a conditional formatting rule that highlights blank cells in column F when the matching due date in column E is at least two days before today.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user manages a task or payment spreadsheet and wants overdue missing entries to stand out automatically.
- Observed behavior
- Blank cells in column F remain unformatted even when the corresponding due date in column E is two or more days in the past.
Open the workbook in WPS Spreadsheets and confirm that due dates are in column E and the blank status cells are in column F. Keep F2 as the active cell when selecting the range so the conditional formatting formula uses the correct relative row.
Use a Conditional Formatting Formula to Highlight Blank Cells After a Due Date
Choose this method to automatically apply a yellow fill to any blank cell in column F when the due date in column E is at least two days before today.
The formula combines three conditions: the due date must not be empty, the due date must be on or before TODAY()-2, and the status cell in column F must be empty. This prevents highlighting rows where a date is missing or where something has already been entered.
Click F2, then drag or press Ctrl+Shift+Down to select F2:F100. Keep F2 as the active cell so relative references start at row 2.
Go to Home > Conditional Formatting > New Rule.
Select “Use a formula to determine which cells to format”.
In the formula box, type =AND(E2<>"",E2<=TODAY()-2,F2=""). This checks the due date in column E and the blank status cell in column F.
Click Format, choose a yellow fill on the Fill tab, and confirm OK until the rule is saved.
Highlight Overdue Blank Status Cells in WPS Spreadsheets
WPS Spreadsheets supports the same Excel-style conditional formatting formula, so you can open your Excel workbook and create the rule without losing formatting or compatibility.
- 1. Download and install WPS Office: Get WPS Office from the official WPS website and install WPS Spreadsheets on your computer.
- 2. Open your Excel workbook: Launch WPS Spreadsheets and open the .xlsx file that contains the due dates and status cells.
- 3. Create the conditional formatting rule: Select F2:F100 and go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
- 4. Enter the formula and choose a fill: Use =AND(E2<>"",E2<=TODAY()-2,F2="") and set the desired yellow highlight.
Frequently Asked Questions
Why do I need E2<>"" in the formula?
It prevents the rule from highlighting blank cells in column F when the due date cell is also empty, so rows with no due date are not flagged as overdue.
Can I use this rule on a different column or row range?
Yes. Change the range to your actual data range and adjust the E and F references so they match the row of the active cell.
How do I change the highlight color?
In the New Formatting Rule dialog, click Format, open the Fill tab, and choose a different fill color before saving the rule.
Why does the rule use TODAY()-2 instead of just TODAY()?
Because the requirement is to highlight only when the due date is at least two days in the past, not immediately on the due date or after one day.
Will the highlights update automatically when I change a due date or fill in a blank cell?
Yes. The TODAY() function recalculates and conditional formatting re-evaluates when cell values change, so highlights update automatically.
