Conditional Formatting Custom Formulas in Google Sheets: 13 Tested Examples

A custom formula rule colors a cell when a formula you write returns TRUE. It is the most flexible kind of conditional formatting in Google Sheets: it can look at other columns, compare dates with today, count duplicates or check another cell. This page collects 13 rules that we applied to a real sheet, with the exact formula and the range each one needs.

Tested in Google Sheets. On November 11, 2026 we applied every rule on this page to the same small task list, including some deliberately broken versions, and checked which cells changed color. How we test

How to add a custom formula rule

  1. Select the cells to format, without the header row. For example A2:E100.
  2. Open Format > Conditional formatting.
  3. Under “Format cells if…”, choose Custom formula is.
  4. Type the formula, starting with =.
  5. Choose the formatting style and click Done.

Our conditional formatting formula generator writes the formula and the range for the most common cases below.

The one rule that explains most problems

Write the formula for the first cell of the range, as if you typed it there. Google Sheets then adjusts it for every other cell, exactly like a formula you copy across and down. Use $ to stop the part that must not move:

  • $B2: the column stays B, the row changes. Use this to color whole rows based on one column.
  • B2: both move. Use this when you only format the column you are checking.
  • $G$1: nothing moves. Use this for a single cell that holds a limit or a setting.

Found in testing: a wrong $ or row number never produced an error. With =B2=TRUE instead of =$B2=TRUE, only the first column of each checked row changed color. With =$B$2=TRUE, every row followed the first checkbox. And with the range starting at row 2 but =$B3=TRUE in the formula, every row followed the checkbox of the row below. Google Sheets accepted all three rules without complaint.

The examples

All examples use this layout: task name in A, checkbox in B, due date in C, amount in D, status in E, with data from row 2. Change the letters and the first row to match your sheet.

# Highlight… Apply to range Custom formula
1 Rows with the checkbox checked A2:E100 =$B2=TRUE
2 Rows whose status is “Late” A2:E100 =$E2="Late"
3 Rows whose task contains “urgent” A2:E100 =ISNUMBER(SEARCH("urgent", $A2))
4 Amounts above the limit in G1 D2:D100 =D2>$G$1
5 Amounts below the limit in G1, ignoring empty cells D2:D100 =AND(D2<>"", D2<$G$1)
6 The top 2 amounts D2:D100 =D2>=LARGE($D$2:$D$100, 2)
7 Duplicate task names A2:A100 =COUNTIF($A$2:$A$100, A2)>1
8 Overdue dates C2:C100 =AND(C2<>"", C2<TODAY())
9 Dates on a weekend C2:C100 =AND(C2<>"", WEEKDAY(C2, 2)>5)
10 Rows with no due date A2:E100 =$C2=""
11 Every other row A2:E100 =ISEVEN(ROW())
12 Checked rows with an amount over 100 A2:E100 =AND($B2, $D2>100)
13 Based on a cell in another sheet any =INDIRECT("'Week 5'!A2")="Apple"

Notes on each one, with what we saw in the test:

1. Checkbox checked

A checked box holds TRUE. =$B2 on its own also worked. Full guide: change row color when a checkbox is checked, or cross the row out instead.

2. Exact text

The comparison ignores case: =$E2="LATE" colored the same rows as "Late". To keep the word in a cell instead of the formula, use =$E2=$G$1; that also matched when G1 held “late” in lower case.

3. Contains text

SEARCH returns a number when it finds the text and an error when it doesn’t, and ISNUMBER turns that into TRUE or FALSE. It found “URGENT: fix report” even though we searched for “urgent” in lower case. Plain =SEARCH("urgent", $A2) also worked in our test, but the ISNUMBER version states the intent and is safer to combine with AND or OR.

4 and 5. Compare with a limit

With 100 in G1, rule 4 colored 120 and 250 but not 100 itself; use >= to include it. In rule 5, the D2<>"" part keeps empty cells white, because Google Sheets treats an empty cell as 0.

6. Top values

LARGE with 2 returns the second largest value, so every cell at or above it is colored: 250 and 120 in our list. Ties can color more cells than N.

7. Duplicates

Both “Send invoice” rows were colored and the unique names were not. The counted range is fully locked ($A$2:$A$100) while A2 moves. To color the whole row, apply the rule to A2:E100 and write $A2 instead of A2. If you use an open-ended range such as $A$2:$A, remember that it counts everything down to the bottom of the sheet, including any other table below your data. To list each value once instead of coloring the repeats, see UNIQUE without blanks. Rules for coloring only the repeats, whole matching rows or case-sensitive duplicates are in how to highlight duplicates.

8. Overdue dates

Without the C2<>"" check, =C2<TODAY() also colored the empty date cell, because an empty cell counts as day zero, long in the past. TODAY() updates daily, so the colors change on their own as dates pass.

9. Weekends

WEEKDAY with type 2 counts Monday as 1 and Sunday as 7, so >5 means Saturday or Sunday. It colored June 1, 2030 and November 14, 2026, both Saturdays.

Found in testing: without the empty-cell check, =WEEKDAY(C2, 2)>5 also colored the empty date cell. An empty cell counts as day zero, December 30, 1899, which was a Saturday. Rules 5, 8 and 9 all need C2<>"" (or D2<>"") for the same reason.

10. Empty cells

=$C2="" colored only the row with no due date.

11. Zebra stripes

ROW() is the row number of each cell, so ISEVEN colors rows 2, 4, 6 and so on. Use ISODD for the other set.

12. Two conditions

AND needs every condition to be true; OR needs one. Both checked rows in our list had amounts over 100, so both were colored.

13. Another sheet

Google’s help page says custom formulas can only reference the same sheet directly and that INDIRECT is the way to reach another one. Our test agreed: the rule ='Week 5'!A2="Apple" was rejected as invalid, while =INDIRECT("'Week 5'!A2")="Apple" worked. INDIRECT takes the reference as text in quotes, so unlike a normal reference it stays the same on every row of the range.

When several rules apply to the same cell

Google’s help page says rules are evaluated in the order listed and the first true rule sets the format. In our test, a row that was both checked (green rule, listed first) and “Late” (orange rule, listed second) stayed green. Drag rules in the panel to change the order. You can also put several styles in one rule: strikethrough, gray text and a fill color together worked in a single rule.

Related guides