Conditional Formatting & Rules
Conditional Formatting Formula: Flag Past-Due Open Invoices & Missing Required Fields
Write row-level AND($C2 <> "Paid", $B2 < $G$2) rules that highlight overdue invoices without false-flagging settled rows or blank rows.
Why a bare =$B2 < TODAY() rule turns every paid historical invoice bright red!
Highlighting past-due receivables with =$B2 < $G$2 alone is a classic accounts-receivable mistake: two problems happen immediately! First, every already-paid invoice from last month still has a Due_Date earlier than the As-Of Date G2, turning 90% of your historical ledger red. Second, blank template rows evaluate empty B2 as numeric 0 (January 0, 1900), lighting up blank rows too!
Locking column references with =AND($B2 <> "", $C2 <> "Paid", $B2 < $G$2) checks three conditions together: the row has a Due_Date, its Status column C is NOT "Paid", and Due_Date precedes As-Of Date G2! Meanwhile, =AND($C2 = "Paid", $D2 = "") catches settled invoices missing a required Payment_Ref!
| Invoice Row | Due Date vs As-Of (2026-10-30) | Status & Payment_Ref | Audit Flag Outcome |
|---|---|---|---|
| INV-401 (Due 2026-10-15) | 15 days earlier | Open / "" | 1 (Past Due — Escalate) |
| INV-402 (Due 2026-10-10) | 20 days earlier | Paid / "ACH-884" | 0 (Paid — Suppressed) |
| INV-403 (Due 2026-10-12) | 18 days earlier | Paid / "" (Blank Ref!) | Missing Payment_Ref Flag = 1 |
Count Past-Due Unpaid Invoices Using Row Rule E2:E5
Sums binary row rule E2 = --AND($B2 <> "", $C2 <> "Paid", $B2 < $G$2) across the receivables table.
=SUM(E2:E5)Count Paid Rows Missing a Required Payment Reference (Blank Audit)
Flags and counts rows marked "Paid" in C2:C5 whose required Payment_Ref column D2:D5 was left blank.
=COUNTIFS(C2:C5, "Paid", D2:D5, "")Try the example
Use the original values or change them to test your understanding.