Conditional Formatting With Custom Formulas
Built-in conditional formatting rules are limited. Custom formulas let you highlight based on any logic you can write.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Highlight the entire row when the status column says Overdue"
=$C1="Overdue"In conditional formatting, this formula is applied to each row. The $ before C locks the column (always check column C), while the row number is relative (adjusts per row). When TRUE, the formatting applies.
Step-by-step breakdown
- $ before C locks the column reference — always checks column C
- Row 1 is relative — Excel adjusts it for each row in the range
- Applied to range like A1:E100 — every cell in the row checks its row's C value
- When $C{row}="Overdue" is TRUE, the entire row gets formatted
- Set up: Home > Conditional Formatting > New Rule > Use a formula
Edge cases & warnings
- Forgetting the $ on the column causes it to check different columns per cell
- The formula must return TRUE or FALSE (or 1/0)
- Rule order matters — rules higher in the list take priority
- Performance: avoid volatile functions (NOW, TODAY) in conditional formatting on large ranges
Examples
"Row with Status="Overdue""
Entire row highlighted in red"Row with Status="Completed""
No formatting appliedFrequently asked questions
Why isn't my conditional formatting formula working?
Most common issues: (1) Missing $ on the column lock, (2) Formula references wrong starting row, (3) The formula doesn't return TRUE/FALSE. Test the formula in a cell first.
Can I use conditional formatting across sheets?
No — conditional formatting formulas can only reference cells on the same sheet. Use helper columns with cross-sheet formulas instead.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.