Excel Conditional Formatting with Formulas: Highlight Rows, Deadlines and Duplicates

The one rule to understand: relative references

A formula rule is written for the top-left cell of the selected range and then shifted for every other cell. To color an entire row based on column D, lock the column but not the row: =$D2="Late". Forgetting the $ is the cause of almost every "only one cell turns red" problem.

Highlight overdue invoices

Select A2:F500, then Home > Conditional Formatting > New Rule > Use a formula. This rule colors unpaid invoices whose due date (column E) has passed. Add a second rule with a softer color for invoices due within 7 days.

=AND($F2<>"Paid";$E2<TODAY())
=AND($F2<>"Paid";$E2>=TODAY();$E2<=TODAY()+7)

Find duplicates on several columns

The preset duplicate rule only checks one column. To flag customers with the same last name AND postcode, count matching pairs with COUNTIFS.

=COUNTIFS($B:$B;$B2;$D:$D;$D2)>1

Zebra stripes and every Nth row

=MOD(ROW();2)=0 shades every other row and keeps working after sorting, unlike manual fill colors. Inside a Table, prefer the built-in banded rows option.

Keep the workbook fast

Each rule is recalculated on every change. Apply rules to exact ranges instead of whole columns, merge duplicated rules after copy-pasting (Manage Rules shows them), and avoid volatile functions like INDIRECT in conditions. Describe the condition in plain words and FormulAI returns the formula to paste into the rule.