Flag overdue invoices and colour the row red automatically
A TRUE/FALSE overdue test and how to use it as a conditional formatting rule in Excel and Google Sheets.
The problem
You want unpaid, late invoices to turn red without checking by hand.
Sample data
Due in column C, Paid in column E (blank = unpaid), as-of date in F (2026-10-25).
Formula
=AND(E3="",C3<F3)
Row 3 (due 2026-10-10, unpaid): TRUE. Row 5 (due in November): FALSE.
Use it as a colour rule
- Excel: Home, Conditional Formatting, New Rule, Use a formula to determine which cells to format.
- Google Sheets: Format, Conditional formatting, Custom formula is.
Paste the formula and pick red. The rule is the same in both; only the menu names differ.
Colour the whole row
Apply the rule to A2:G5 and anchor the columns: =AND($E2="",$C2<$F$2). This is the same test. The company's separate check of this anchored form on three sample rows gave TRUE for the unpaid overdue row and FALSE for the paid row and the unpaid not-yet-due row.
Tested on sample data by the Cellwise checker (log CW-PL-20261018-41). AI-made: the text, voice and visuals are generated by AI.
Checked before publishing: a check.