Excel conditional formatting practice exercises
5 graded problems with realistic data, from medium to hard.
Not a function, but a rule. Automatically highlights any cell in a range that meets a condition, and keeps highlighting correctly as the data changes. The 5 exercises below come from LearnExcel's practice library. Each one gives you a realistic table, a cell to fill, and a hint if you get stuck. Read them here, then solve them in the interactive grid, where your formula is checked the moment you press Enter.
New to the function? Read the Conditional formatting guide first: syntax, a worked example and the mistakes people make.
In this set
- Highlight reps over $300
- Flag low stock visually
- Flag the "needs review" score band
- Catch a duplicate ticket number
- Highlight the top 3 products
The exercises
A sales table lists this month's revenue by rep. Highlight every rep who brought in more than $300, so they jump out at a glance.
| A | B | |
|---|---|---|
| 1 | Rep | Revenue |
| 2 | Maya | 320 |
| 3 | Owen | 210 |
| 4 | Priya | 450 |
| 5 | Lucas | 150 |
| 6 | Farah | 380 |
Add the conditional formatting rule.
Show a hint
Select B2:B6, choose "Greater than" for the rule, and enter 300.
Highlighting values above a threshold (top performers, over-budget lines, at-risk metrics) is the single most common conditional formatting rule.
A stockroom sheet lists units left per product. Highlight every item with fewer than 10 units left so reorders are obvious without reading every number.
| A | B | |
|---|---|---|
| 1 | Product | Units Left |
| 2 | Mugs | 4 |
| 3 | Notebooks | 22 |
| 4 | Stickers | 8 |
| 5 | Pens | 15 |
| 6 | Folders | 11 |
Add the conditional formatting rule.
Show a hint
Select B2:B6, choose "Less than", and enter 10.
"Less than" mirrors "Greater than" exactly. It's the same rule in the other direction, and just as common for low-stock or under-target alerts.
Scores from 60 to 75 (inclusive) fall into a "needs review" band: not a clear pass, not a clear fail. Highlight exactly those.
| A | B | |
|---|---|---|
| 1 | Student | Score |
| 2 | Lea | 92 |
| 3 | Noah | 68 |
| 4 | Ines | 55 |
| 5 | Marco | 74 |
| 6 | Priya | 61 |
Add the conditional formatting rule.
Show a hint
Select B2:B6, choose "Between", and enter 60 and 75. Both ends count.
A "between" rule catches a middle band a single greater-than or less-than rule can't express in one pass.
A guest list may have an accidental double-entry. Highlight every ticket number that appears more than once in the list.
| A | B | |
|---|---|---|
| 1 | Guest | Ticket # |
| 2 | Aya | 4471 |
| 3 | Ben | 5820 |
| 4 | Cleo | 4471 |
| 5 | Dax | 3390 |
| 6 | Eli | 5820 |
Add the conditional formatting rule.
Show a hint
Select B2:B6 and choose "Duplicate values". No threshold to type; it flags any value that shows up more than once.
Spotting accidental duplicates (double-booked tickets, repeated invoice numbers, the same ID entered twice) is a rule you'll reach for on almost any imported list.
Highlight this quarter's 3 highest-grossing products, not a fixed dollar cutoff, whichever three happen to be on top.
| A | B | |
|---|---|---|
| 1 | Product | Revenue |
| 2 | Widget | 400 |
| 3 | Gadget | 950 |
| 4 | Gizmo | 300 |
| 5 | Doohickey | 700 |
| 6 | Thingamajig | 500 |
Add the conditional formatting rule.
Show a hint
Select B2:B6, choose "Top N", and enter 3. The rule finds the top 3 for you, no hand-picking a dollar amount.
"Top N" is what a fixed threshold can't do reliably. It stays correct even when next quarter's numbers move the whole range up or down.
Solve these in a real grid
Type your formula, get graded instantly. Your first 10 exercises are free, no signup.
Start the Conditional formatting exercises →