To flag overdue work automatically in Excel, select the task rows, create a conditional formatting rule using a formula, and compare each due date with TODAY(). A reliable rule also ignores blank or invalid dates and excludes tasks already marked Done. This walkthrough builds three distinct alerts—overdue, due today, and due within three days—without changing your underlying data.
What should an overdue-task rule check?
An overdue task is one whose due date is earlier than the review date and whose status is not Done. A due date alone is insufficient: an already completed task should not remain red, and an empty due-date cell should not become an imaginary overdue item. Conditional formatting changes appearance, not task ownership, due dates, or status values.
Use one row per request, consistent status labels, and actual Excel date values. If your team uses Complete instead of Done, replace the exact status text in every formula. For consistent entries, see our Excel drop-down list guide; it explains how to prevent conflicting labels in a shared tracker.
Build a small Excel task tracker with known outcomes
On a worksheet named Tracker, enter Task ID, Owner, Status, Due Date, Priority, and Notes in cells A1:F1. Put Review date in G1 and =DATE(2026,11,2) in H1. The fixed review date makes the following example reproducible; later, replace H1 with =TODAY() for a live tracker.
| Row | Task ID | Owner | Status | Due Date | Expected alert |
|---|---|---|---|---|---|
| 2 | T-101 | Maya | Open | 2026-10-30 | Overdue |
| 3 | T-102 | Leo | Done | 2026-10-29 | None |
| 4 | T-103 | Priya | Blocked | 2026-11-02 | Due today |
| 5 | T-104 | Maya | Open | 2026-11-04 | Due soon |
| 6 | T-105 | Leo | Open | 2026-11-10 | None |
| 7 | T-106 | Priya | Open | Blank | None |
| 8 | T-107 | Maya | Open | 2026-11-01 14:00 | Overdue |
Enter the dates as genuine Excel dates; row 8 contains a date and time. Format column D to show dates clearly. Leave D7 genuinely empty rather than typing the word Blank. Before adding any rules, the expected counts are two overdue rows, one due today, one due soon, and three with no alert.
How do you highlight entire overdue rows?
Select A2:F100 so the alert colors the full task record. On desktop Excel, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter this formula, choose a red fill, and confirm the rule:
=AND(ISNUMBER($D2),INT($D2)<$H$1,$C2<>"Done")The ISNUMBER test excludes empty cells and dates stored as text. INT strips the time portion from valid date-time values, so a task due yesterday at 2 p.m. is overdue throughout today. $D2 locks the Due Date column while allowing the row number to move; $H$1 always points to the same review date.
The formula is evaluated relative to the upper-left cell of the selected range. For A2:F100, the first formula must refer to row 2, not row 1 or row 100. Microsoft’s AND and conditional formatting instructions describe the formula-based rule workflow and Boolean conditions.
With H1 set to 2 November 2026, rows 2 and 8 should turn red. Row 3 stays uncolored even though its date has passed, because its status is Done. This distinction is important when a report mixes completed and outstanding requests.
How do you mark tasks due today and within three days?
Create two more formula rules with the same Applies to range, A2:F100. For due today, choose a blue fill and use:
=AND(ISNUMBER($D2),INT($D2)=$H$1,$C2<>"Done")For the next three calendar days after today, choose an amber fill and use:
=AND(ISNUMBER($D2),INT($D2)>$H$1,INT($D2)<=$H$1+3,$C2<>"Done")These three rules do not overlap: overdue means before the review date, due today means equal to it, and due soon means the following one through three calendar days. The example therefore colors row 4 blue and row 5 amber. A weekend is still counted as a calendar day; business-day deadlines need a separately agreed calendar and holiday rule.
To make the alerts live, replace H1 with =TODAY(). Excel calculates TODAY from the computer or spreadsheet environment’s current date when the workbook recalculates. It is not a real-time notification system: someone still needs to open or refresh the workbook and act on the highlighted rows.
How should you order multiple conditional formatting rules?
In Manage Rules, inspect the list from top to bottom. When two rules assign competing formats to the same cell, rule priority and any Stop If True setting can affect which appearance wins. Our overdue, due-today, and due-soon formulas deliberately use mutually exclusive date comparisons, so a valid unfinished task should qualify for only one alert. That makes the colors easier to explain and reduces dependence on priority.
Before deploying the tracker to colleagues, change the review date in H1 to the day before and the day after one sample deadline. Confirm that the same row moves between due soon, due today, and overdue, while a Done row stays neutral. Restore the live =TODAY() formula afterward. This is a quick manual example check, not a guarantee that every future import will contain clean dates.
Microsoft also documents formula-based conditional formatting for Excel for Mac. Menu names and the available editing controls can differ between Windows, Mac, and Excel for the web; keep the formula logic and selected range consistent even if the dialog looks different.
Why do some dates refuse to highlight?
Start with the cell’s data type. A date that looks like 2026-11-01 may be plain text imported from CSV. In an unused cell, enter =ISNUMBER(D2); TRUE means Excel recognizes the value as numeric, which is how ordinary Excel dates are stored. FALSE means the formatting rule intentionally ignores it.
Next inspect Home > Conditional Formatting > Manage Rules. Choose the current worksheet and confirm the Applies to range is =$A$2:$F$100. A rule applied only to D2:D100 will color dates rather than whole rows. A formula beginning with $D$2 locks every row to the first task and can color unrelated records.
Check the status vocabulary too. A task marked Completed will still be treated as unfinished when the formula excludes only Done. Agree on a single completion label, correct imported variations, and avoid adding a growing chain of special cases to each rule. Finally, confirm H1 contains a real date, not text typed with an ambiguous day/month order.
How do you maintain the rules when the tracker grows?
For a short-lived sheet, extend the Applies to range when you add tasks below row 100. For a longer-running register, consider an Excel Table so new rows inherit a consistent layout; still check the conditional formatting range after inserting or pasting data. Copying cells from other workbooks can introduce extra rules or inconsistent formatting, so review Manage Rules if colors suddenly behave differently.
Keep the tracker separate from summary reporting. For example, =COUNTIFS(C2:C100,"Open",D2:D100,"<"&H1) counts open rows with earlier dates, but unlike the formatting rule it does not exclude text-date anomalies by testing ISNUMBER and does not include Blocked tasks. Our COUNTIF and COUNTIFS examples explain how to define and check reporting criteria before sharing a total.
Review deadlines and ownership at least weekly. A red row identifies a potential problem, not its cause; someone still needs to confirm the date, obtain missing input, and escalate when necessary. The recurring deadline calendar guide shows how to schedule preparation checkpoints rather than waiting for overdue alerts.
Can conditional formatting replace a project status report?
No. Conditional formatting is a visual control for a worksheet, not a substitute for documented updates or decisions. It cannot tell whether a blocked request is waiting on a vendor, whether an owner has accepted a revised date, or whether a completed task needs approval. Use the colors to decide which records deserve attention, then record the action and owner in the tracker.
If you need summaries by owner or priority, first keep the source clean and use a separate report. Our Excel PivotTable walkthrough covers how to check source data and validate totals. That reporting step is complementary to the row-level alerts, not a replacement for them.
Final checklist for a dependable deadline tracker
Use real dates, one completion label, a clear review date, and three nonoverlapping rules. Keep the first-row reference aligned with the selected range, verify the seven example outcomes, and document who reviews overdue items. With those basics in place, Excel can surface urgent work consistently while the team remains responsible for decisions and follow-through.