# Conditional formatting with formulas (Excel lesson 9 of 45)

> A formula rule colors exactly the cells you mean, and keeps up when the data changes.

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

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Data tools"
- **Time:** about 10 minutes, 3 graded exercises
- **Functions:** `Conditional formatting`, `COUNTIF`, `$`
- **Access:** free
- **Key formula:** `=C2>$E$2`: Above the limit in E2, for every row

## How it works

The built-in rules (greater than, top 10, duplicate values) cover simple cases. For anything else, select the cells, then go to Home > Conditional Formatting > New Rule and choose **Use a formula to determine which cells to format**. Type a formula that returns TRUE for the cells to color, click Format to pick a fill, and press OK.

Write the formula for the *first* cell of your selection, the active cell. If you selected `C2:C15`, the rule `=C2>500` is about C2. Excel then moves it the way it moves any formula you fill down: C3 checks C3, C4 checks C4.

That is why the $ signs matter. Point at a settings cell with both dollar signs, `=C2>$E$2`, so every row compares against the same limit. To color whole rows, select every column and lock only the column of the cell you test: `=$C2<$F$2`. Each cell in a row then looks at that row's column C.

COUNTIF makes a duplicate rule: `=COUNTIF($B$2:$B$13,B2)>1` is TRUE when a value appears more than once in the locked list. The practice grid below the sheet works like the New Rule dialog and lights up the matches as you type.

## Syntax

| Formula | What it does |
|---|---|
| `=C2>$E$2` | Above the limit in E2, for every row |
| `=$C2<$F$2` | Whole row, when column C is before the date in F2 |
| `=COUNTIF($B$2:$B$13,B2)>1` | Values that appear more than once |
| `=$D2="Overdue"` | Whole row, by the text in column D |

## Watch out for

- Write the rule for the first cell you selected. A rule written for row 1 on a selection that starts at row 2 is off by a row.
- No $ on the settings cell and the rule slides past it: C3 compares to E3, C4 to E4, and most of those are empty.
- Typing the number into the rule (=C2>500) works until the limit changes. Point at a cell instead.
- The formula must return TRUE or FALSE. A rule like =C2 colors every cell that isn't zero.

## The exercises

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

1. **Over the limit.** Highlight the claims in C2:C15 that are over the approval limit in E2. Write the rule for C2 and point at the limit cell, so a new limit recolors the list.
2. **Overdue rows.** Color every row of A2:D13 whose due date in column C is before the report date in F2. The rule applies to all four columns, so write it for A2, the first cell of the range.
3. **Repeat customers.** Highlight the emails in B2:B13 that appear more than once in the list. Use COUNTIF, and write the rule for B2.

Previous: [COUNTIFS, SUMIFS & AVERAGEIFS](https://spreadsheetpractice.com/learn/countifs-sumifs-averageifs) · Next: [PivotTables](https://spreadsheetpractice.com/learn/pivottables)

---

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