# Conditional formatting with custom formulas (Google Sheets lesson 5 of 17)

> Color cells, and whole rows, by any rule you can write as a formula.

Source: https://spreadsheetpractice.com/google-sheets/learn/conditional-formatting · Updated October 29, 2026

- **Course:** [Google Sheets course](https://spreadsheetpractice.com/google-sheets), track "Data tools"
- **Time:** about 10 minutes, 3 graded exercises
- **Functions:** `Custom formula`, `$`, `AND`, `COUNTIF`
- **Access:** free
- **Key formula:** `=C2>$E$2`: Compare each cell with the limit in E2

## How it works

Conditional formatting changes how a cell looks, not what it holds. Select the cells, open **Format > Conditional formatting**, and the "Conditional format rules" panel opens with your selection under "Apply to range". The "Format cells if…" list has ready-made rules such as "Greater than" and "Text contains". At the bottom of that list is **Custom formula is**, which takes any formula that comes out TRUE or FALSE. Pick a formatting style and click Done.

Write the custom formula for the first cell of the range. Sheets runs it again for every other cell, shifting the references exactly as if you had filled the formula across the range. With the rule applied to C2:C11, `=C2>1500` checks C3 for the next cell, C4 for the one after, and so on.

Dollar signs decide what stays put. To compare every cell with a limit in E2, lock the limit: `=C2>$E$2`. To color whole rows, apply the rule to all the columns, such as A2:D10, and lock only the column you test: `=$D2="Blocked"`. Every cell in row 2 then looks at D2, so the row lights up as one.

Because any formula works, the same idea covers duplicates, `=COUNTIF($A$2:$A$12,A2)>1`, and dates, `=$C2<$F$2` for anything due before the date in F2. The format only changes the look. To count the highlighted rows, use the same test inside COUNTIF or COUNTIFS; Sheets has no built-in function that counts by color.

## Syntax

| Formula | What it does |
|---|---|
| `=C2>$E$2` | Compare each cell with the limit in E2 |
| `=$D2="Blocked"` | Applied to A2:D10: color the whole row |
| `=AND($C2<$F$2,$D2<>"Done")` | Due before the date in F2 and not done |
| `=COUNTIF($A$2:$A$12,A2)>1` | Values that appear more than once |
| `=$C2<TODAY()` | Dates before today, rechecked every day |

## Watch out for

- Write the formula for the first cell of "Apply to range". If the range starts at row 2 and the formula says C1, every cell checks the row above it.
- Without $ on the limit, =C2>E2 slides down to E3, E4 and so on, which are empty.
- For whole rows, lock the column ($D2) but not the row. $D$2 would make every row copy row 2.
- When two rules match the same cell, the one higher in the list sets the format. Drag the more specific rule to the top.

## The exercises

Solve them in the live grid at [Conditional formatting with custom formulas](https://spreadsheetpractice.com/google-sheets/learn/conditional-formatting). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **Amounts over the limit.** Highlight the invoice amounts in C2:C11 that are above the limit in E2. The rule is applied to C2:C11: write its custom formula, and lock the reference to the limit.
2. **Past-due rows.** Color the whole row of every task that is past due: its due date in column C is before the date in F2, and its status in column D isn't "Done". The rule is applied to A2:D10.
3. **Double bookings.** Highlight every booking reference in A2:A12 that appears more than once. The rule is applied to A2:A12.

Previous: [FILTER and SORT, the Sheets way](https://spreadsheetpractice.com/google-sheets/learn/filter-and-sort) · Next: [Pivot tables](https://spreadsheetpractice.com/google-sheets/learn/pivot-tables)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
