Highlight Overdue Tasks in an Excel Tracker with Conditional Formatting
How do I highlight overdue task rows in an Excel tracker when the due date has passed and the task is not Done?
Select the task rows, add a conditional formatting rule of the type Use a formula to determine which cells to format, and enter =AND($A2<>"",$C2<>"",$C2<TODAY(),$D2<>"Done"). The dollar signs lock the columns so every cell in a row checks that row's due date and status. A helper column can label each row Overdue or Due today.
Select the task rows, create a conditional formatting rule that uses a formula, and enter =AND($A2<>"",$C2<>"",$C2<TODAY(),$D2<>"Done"). Every row with a task name, a due date before today and a status other than Done gets the fill you choose. A second formula in a helper column writes Overdue or Due today next to each task, so you can filter and count them as well as see them.
Which conditional formatting formula highlights an overdue task row in Excel?
The rule needs to answer one question per row: is there a task here, does it have a due date, is that date in the past, and is the task still open? AND returns TRUE only when all four parts are true.
=AND($A2<>"",$C2<>"",$C2<TODAY(),$D2<>"Done")
Here is what each part does, assuming the tracker below:
$A2<>""skips rows with no task name, so empty rows at the bottom of the range stay plain.$C2<>""skips tasks with no due date. Without it, Excel treats an empty date cell as zero, which is earlier than today, and every undated task would light up.$C2<TODAY()checks whether the due date is before today. TODAY returns the serial number of the current date, the same kind of number Excel stores behind every date, so the comparison works on real dates.$D2<>"Done"leaves finished tasks alone. Excel compares text without regard to case, sodoneandDONEcount as Done too.
The dollar sign before each column letter is what makes the whole row change color. The rule is applied to every cell from column A to column E. When Excel checks cell B5 or E5, the locked column letters still point at A5, C5 and D5, because the row number has no dollar sign and shifts with each row. If you write C2 without the dollar sign, cell B2 would check D2, cell C2 would check E2, and the highlight would come out patchy.
A task due today does not count as overdue in this rule. That is deliberate: something due today can still be done on time. The helper column in the next section gives it its own label.
How do you set up the sample task tracker?
Put the headers in row 1 and the tasks from row 2 down. The names and tasks are fictional, and the dates assume today is 16 November 2026.
| Row | A: Task | B: Owner | C: Due date | D: Status | E: Flag (helper) |
|---|---|---|---|---|---|
| 2 | Renew domain | Avery Example | 10 Nov 2026 | Open | Overdue |
| 3 | Send payroll file | Riley Placeholder | 16 Nov 2026 | In progress | Due today |
| 4 | Order laptops | Morgan Sample | 12 Nov 2026 | Done | (blank) |
| 5 | Update handbook | Avery Example | (empty) | Open | (blank) |
| 6 | Book team venue | Riley Placeholder | 20 Nov 2026 | Open | (blank) |
| 7 | (empty row) | (blank) | |||
| 8 | File VAT return | Morgan Sample | 15 Nov 2026 | (empty) | Overdue |
| 9 | Chase late invoice | Avery Example | 16 Nov 2026 | done | (blank) |
With the rule in place, rows 2 and 8 are shaded and nothing else is. Row 3 is due today, so it is labeled but not shaded. Row 4 is past its date but marked Done. Row 5 has no date. Row 8 has no status at all, and an empty status is not Done, so it counts as overdue. Row 9 says done in lower case, which Excel treats as Done.
Before adding any rule, check that column C holds real dates. Select a date cell and look at the number format on the Home tab: it should say Date or a custom date format, not Text. A date typed as text, or pasted in from another system with a leading apostrophe, is never less than TODAY() in Excel, so that row will never turn red. If dates sit on the left of the cell by default, they are probably text.
How do you add the rule in Excel step by step?
Microsoft documents the desktop route through the rules manager in its guide to using conditional formatting to highlight information. In the desktop version of Excel for Windows:
- Select
A2:E100, starting from cell A2. Go further down than your last task so new rows are covered. - On the Home tab, in the Styles group, choose Conditional Formatting > Manage Rules, then New Rule.
- Under Select a Rule Type, choose Use a formula to determine which cells to format.
- In the box labeled Format values where this formula is true, type the formula. It must start with an equals sign.
- Choose Format, pick a fill color on the Fill tab, and choose OK until the dialogs close.
Type the formula rather than clicking cells while the box is open. Microsoft notes that selecting cells on the sheet inserts absolute references such as $C$2, which would make every row check row 2 only.
The formula is written for row 2 because the selection starts at A2. If your data starts on another row, change every 2 in the formula to that row number.
In Excel for the web, the same Microsoft page describes a pane instead of a dialog: select the cells, choose Home > Conditional Formatting > New Rule, confirm the range under Apply to range, set Rule Type to Formula, enter the formula and select Done.
If the rule does nothing, Microsoft’s page points out that an invalid formula applies no formatting at all, with no error message. Paste the formula into an empty cell on row 2 first. It should show TRUE or FALSE. A #NAME? or #VALUE! result tells you what to fix.
Which helper column formula labels rows Overdue or Due today?
Shading tells you where to look, but you cannot filter or count by color without extra steps. A label in column E fixes that. Enter this in E2 and fill it down to the last row of the tracker:
=IF(A2="","",IF(OR(C2="",D2="Done"),"",IF(C2<TODAY(),"Overdue",IF(C2=TODAY(),"Due today",""))))
Read it from the outside in. If there is no task name, the cell stays blank, which keeps empty rows clean. If the date is missing or the status is Done, the cell stays blank. Otherwise a date before today returns Overdue, a date equal to today returns Due today, and a future date returns nothing.
The helper column needs no dollar signs, because each copy only looks at its own row. Once it is filled down, you can use AutoFilter on column E to see only overdue tasks, or count them with =COUNTIF(E2:E100,"Overdue"). If you hide rows and want a total of only the visible ones, the approach in summing only visible task counts after filtering in Excel applies here too.
With the helper column in place, the conditional formatting rule can be shorter:
=$E2="Overdue"
This version is easier for a colleague to read, and the logic lives in one place. The trade-off is that someone who deletes or overwrites column E also breaks the highlight. Keep the longer rule if the tracker is shared widely and column E might be edited by hand.
You can add a second rule with =$E2="Due today" and a lighter fill. In Manage Rules, keep the Overdue rule at the top. Rules run from top to bottom, and the two labels never apply to the same row, so they will not clash.
What breaks the overdue highlight in a real tracker?
Most problems come from the data, not the formula. The table lists the common ones and what to do.
| Symptom | Likely cause | Fix |
|---|---|---|
| An old task is never shaded | The due date is stored as text | Re-enter the date or convert the column to a date format |
| Every undated task is shaded | The $C2<>"" check was left out |
Use the full rule from this guide |
| Only one column of each row changes color | Column letters are not locked with $ |
Use $C2 and $D2, not C2 and D2 |
| A Done task is still shaded | The status has a trailing space or a different word, such as Complete | Use a drop-down list for status, or change "Done" to your word |
| Due today shows as blank | The date cell also holds a time of day | Strip the time, or compare INT(C2) with TODAY() |
| Colors do not change the next morning | Workbook calculation is set to Manual | Set File > Options > Formulas > Calculation options to Automatic |
The last row comes from Microsoft’s TODAY documentation, which says to check that Automatic is selected under the calculation options if the date does not update. TODAY reflects the clock of the computer that opens the file. A teammate in a different time zone may see a task flip to Overdue a few hours earlier or later than you do.
If your team uses more than one finished status, such as Done and Cancelled, extend the check: AND($A2<>"",$C2<>"",$C2<TODAY(),$D2<>"Done",$D2<>"Cancelled"). Apply the same change to the OR in the helper column.
Due dates that skip weekends and holidays are a separate problem. If your tracker calculates them, see counting workdays and setting due dates with holidays. That guide is written for Google Sheets; Excel has its own WORKDAY function, so check its arguments in Microsoft’s documentation before copying the formula over. If the owner column is filled from a project list, looking up a project owner by ID without approximate matches keeps the names exact.
When is a highlight rule the wrong tool for overdue tasks?
Color is a visual cue only. It does not notify anyone, and it does not survive every export. A CSV export drops all formatting, and some people cannot tell red from green fills apart. The helper column covers both gaps, which is one more reason to keep it.
If overdue items need someone to act, a highlighted cell in a file nobody opens will not do it. A reminder sent from the tracker, or a task tool with due date alerts, fits better. The pattern in finding open CRM contacts with no recent follow-up uses the same past-date logic to build a working list rather than a colored one.
Large trackers with thousands of rows and many formula rules can slow down, because TODAY makes every rule recalculate. Keep the Applies to range close to the real data instead of whole columns. For more spreadsheet reporting patterns, see the spreadsheets and data topic page.