Choose what you want to highlight, tell the generator where your data is, and copy the two things Google Sheets asks for: the range to apply the rule to and the custom formula. Each result explains where the dollar signs go and why, which is where most conditional formatting rules go wrong. Nothing you type leaves your browser.
Apply to range
Custom formula is
In Google Sheets: Format > Conditional formatting, paste the range into “Apply to range”, choose “Custom formula is” under “Format cells if…”, paste the formula and pick a color.
What the generator can build
| Highlight when… | Formula for row 2 (whole row, column B or C or D) |
|---|---|
| A checkbox is checked | =$B2=TRUE |
| A cell equals a text or number | =$E2="Late" |
| A cell contains some text | =ISNUMBER(SEARCH("urgent", $A2)) |
| A number is greater than a value | =$D2>100 or =$D2>$G$1 |
| A number is less than a value | =AND($D2<>"", $D2<100) |
| A date is in the past | =AND($C2<>"", $C2<TODAY()) |
| A date falls on a weekend | =AND($C2<>"", WEEKDAY($C2, 2)>5) |
| A cell is empty | =$C2="" |
| A value appears more than once | =COUNTIF($A$2:$A$100, $A2)>1 |
| A number is in the top N | =$D2>=LARGE($D$2:$D$100, 3) |
| Every other row | =ISEVEN(ROW()) |
We applied the formulas the generator produces to a test sheet in Google Sheets and checked which cells changed color. Our guide to conditional formatting custom formulas shows the results, including the mistakes that color the wrong cells without any warning.
Tips
- Start the range on your first data row. The formula is written for the first row of the range, so leave the header out: A2:E100, not A1:E100.
- Open-ended counting ranges. If you type a range such as
A2:E, duplicate and top-N rules count down to the bottom of the sheet, including any other data you keep below the table. - Comma or semicolon. If your spreadsheet uses semicolons between arguments (most European and Latin American locales), choose that option. Our formula checker converts any formula between the two.
- Values that look like cells. In the value box, something like
G1is read as a cell reference. To match the literal text “G1”, put it in a cell and point the rule at that cell.