Highlight Overdue Rows Automatically

Excel & Google Sheets

How it works

This formula lives inside a conditional formatting rule applied to the whole table, say $A$2:$C$20. It returns TRUE — and triggers the highlight — only when both conditions hold: the due date in column B is before today, and the status in column C isn't Complete. The $ placement is what makes whole-row highlighting work: $B2 locks the column but not the row, so when the rule evaluates a cell in column A or C of row 2, it still checks B2's date and C2's status. Every cell in a row therefore gets the same TRUE/FALSE answer, and the entire row lights up together. Because TODAY() recalculates daily, rows turn red on their own the day after their deadline passes. Set it up via Home > Conditional Formatting > New Rule > "Use a formula" in Excel, or Format > Conditional formatting > "Custom formula is" in Google Sheets, with the full table selected.

$B2<TODAY()

TRUE when the due date is before today. The $ locks column B so every cell in the row checks the same date.

$C2<>"Complete"

TRUE when the status isn't Complete — finished tasks never highlight, however old.

AND(…)

Both conditions must be TRUE: past due and still open.

When to use it

Use this on any tracker with deadlines: project tasks, invoices awaiting payment, contract renewals, license expirations. It turns the sheet into a self-updating early-warning system.

Common mistakes

• ✕Forgetting the $ before the column letters.

  ✓With plain B2 and C2, the references shift as the rule moves across columns, so column A checks A2 and column C checks D2 — the highlighting looks random. Lock the columns: $B2 and $C2 (but leave the row number unlocked).

• ✕Blank due dates get highlighted as overdue.

  ✓An empty date cell counts as zero, and zero is before today, so rows with no deadline turn red. Exclude them: =AND($B2<>"",$B2<TODAY(),$C2<>"Complete").

• ✕Dates stored as text never trigger the rule.

  ✓Text like "07/03/2026" can't be compared to TODAY(), so nothing highlights. Check with =ISNUMBER(B2) — if FALSE, convert the column to real dates (Data > Text to Columns in Excel, or Format > Number > Date in Sheets).

• ✕Applying the rule to only one column and expecting full rows to color.

  ✓The rule only formats the cells it's applied to. Select the entire table range ($A$2:$C$20) before creating the rule, not just the date column.