Conditional Formatting With Custom Formulas
Conditional Formatting With Formulas is a Excel function that in conditional formatting, this formula is applied to each row. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Built-in conditional formatting rules are limited. Custom formulas let you highlight based on any logic you can write.
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 any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.