To highlight overdue items automatically in Excel, apply conditional formatting with =AND($C2<TODAY(), $D2<>"Complete") for red, and =AND($C2>=TODAY(), $C2<=TODAY()+7, $D2<>"Complete") for a yellow approaching-deadline warning. Because TODAY recalculates on open, the colors update themselves with no manual review. Excluding completed items in the formula is what stops finished work showing as overdue forever.
Deadlines slip when they’re buried in a spreadsheet. Conditional formatting makes overdue items and approaching deadlines impossible to miss: red for past due, yellow for due soon, green for on track. No manual checking, no missed dates. The formatting updates automatically every time you open the file.
Download the finished workbook (.xlsx, free, no sign-up)
Sample task list with due dates
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Task | Assigned To | Due Date | Status |
| 2 | Invoice #4021 | Sarah | 3/28/2025 | Open |
| 3 | Quarterly Report | James | 4/10/2025 | Open |
| 4 | Client Proposal | Maria | 4/25/2025 | Open |
| 5 | Budget Review | David | 4/15/2025 | Complete |
The colors show the list as it looked on April 5, 2025. In your own file they follow the day you open it.
Rule 1: Highlight Overdue Items (Red)
1Select the rows that hold tasks (A2:D5 here)
2Go to Home → Conditional Formatting → New Rule
3Select “Use a formula to determine which cells to format”
4Enter:
=AND($C2<TODAY(), $D2<>"Complete")
Highlights the entire row red when the due date is past AND the status isn’t “Complete”. The dollar sign on $C locks the column reference so it works across the row.
Set the format to a light red fill with dark red text.
Selecting extra rows for future tasks?
An empty row counts as overdue, because a blank due date is treated as 0, which is before today. To select extra rows such as A2:D100, add a blank check to the red rule: =AND($C2<>"", $C2<TODAY(), $D2<>"Complete"). The yellow and green rules need no change.
Rule 2: Highlight Approaching Deadlines (Yellow)
Same steps, but with this formula:
=AND($C2>=TODAY(), $C2<=TODAY()+7, $D2<>"Complete")
Highlights rows where the due date is within the next 7 days and the task isn’t complete. Change 7 to any number of days for your warning window.
Rule 3: Highlight On-Track Items (Green)
=OR($D2="Complete", $C2>TODAY()+7)
Green for completed items or items with more than 7 days remaining.
Rule priority matters
In the Conditional Formatting Rules Manager, make sure Overdue (red) is at the top, then Approaching (yellow), then On Track (green). Excel puts each new rule at the top of the list, so after adding them in the order above, move Overdue (red) back up with the arrows. Excel applies rules top-down, so the first matching rule wins. Check “Stop If True” on each rule to prevent overlap.
Adding a Days Remaining Column
For a countdown in words alongside the color coding, such as “5 days left”, “Overdue by 3 days”, or “Done”:
=IF(D2="Complete", "Done", IF(C2<TODAY(), "Overdue by "&TEXT(TODAY()-C2,"0")&" days", C2-TODAY()&" days left"))
TODAY() recalculates
TODAY() updates every time the workbook opens or recalculates. This means your conditional formatting is always current, but it also means printing the same file on different days will show different highlights. If you need a fixed snapshot, paste the date as a static value.
If the due dates are on invoices, EG Quote & Invoice tracks them with no rules to set up: past-due invoices show the due date and how many days late, and the Pro edition drafts a payment reminder for any late invoice in one click. More in tracking invoices and payments in Excel.
Common questions
Why have the colors not updated?
TODAY recalculates when the file opens or when something triggers a recalculation. If the file has been open since yesterday, press F9 to force it.
Can I sort or filter by these colors?
Yes. Filter by Color works on conditional formatting, not only manual fills, so you can pull all overdue rows together.
How do I stop completed items showing as overdue?
Include the status in the rule: =AND($C2<TODAY(), $D2<>"Complete"). Without the second condition, finished work stays red forever.
When Formulas Aren’t Enough
Conditional formatting highlights problems visually, but it can’t take action on them:
- Email notifications: automatically alerting the assigned person when their task is approaching or overdue
- Dashboard rollup: a summary showing how many tasks are overdue, approaching, and on track across all departments
- Escalation rules: if overdue by 3+ days, flag it to the manager; if 7+ days, escalate to the director
- Recurring deadline tracking: monthly tasks that auto-generate new deadlines when completed
- Multi-workbook consolidation: tracking deadlines across project files from different teams
A custom VBA deadline tracker monitors dates, sends alerts, builds dashboards, and manages escalation, keeping nothing from slipping through the cracks.
“Outstanding Service! Helped us while on his vacation! My Excel Guy! 10 Stars”
Darryl H. · Repeat client · PeoplePerHour
Need Automated Deadline Tracking with Alerts?
Tell us about your workflow and we’ll build a tool that keeps everything on schedule.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.