Excel Conditional Formatting: Highlight Overdue Dates Automatically

Coloring overdue rows by hand works until you miss one. It also has to be redone every day, because yesterday's "due tomorrow" is today's "due today" and tomorrow's "late."

TL;DR: Select the due-date cells, add a Highlight Cells Rules > Less Than rule, and use =TODAY() as the value.

Steps

1. Select the cells with due dates. Go to Home > Conditional Formatting > Highlight Cells Rules > Less Than.
2. In the value box, enter =TODAY() and confirm. Only dates earlier than today turn red.
3. Edit a due date and the color updates immediately. There is nothing to repaint.

Because the rule compares against today's date, cells that become overdue tomorrow will turn red tomorrow without you touching the file.

Checked in the free Excel for the web.

Where it fits

Task trackers, invoice due dates, contract renewal lists, document review deadlines. Anywhere a column of dates answers the question "is this late?", this rule answers it visually.

Things that trip people up

Empty cells in the selected range can be treated as zero, which is earlier than today, so they may light up too. Select only the rows that contain dates, or add a separate rule for blanks.

Dates typed as text (for example with an extra space or a format Excel didn't recognize) are not real dates, and the comparison won't work reliably on them. If one row refuses to change color, check whether that cell is actually a date.

Use =TODAY(), not a typed date. A fixed date in the rule means the highlighting freezes on that day.

The same rule from Python

If you generate the workbook with openpyxl, you can attach the rule in code so every exported file already has it:

from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill

red = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
ws.conditional_formatting.add(
    "C2:C50",
    CellIsRule(operator="lessThan", formula=["TODAY()"], fill=red),
)

The rule is stored in the file and evaluated by Excel when the workbook is opened, so the result is the same as setting it up by hand. Coloring cells in the script itself would only be correct on the day the file was generated.

AI helped draft this article. The rule was set up and tested in Excel for the web.