Excel practice · Data Tools

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

  1. Highlight reps over $300
  2. Flag low stock visually
  3. Flag the "needs review" score band
  4. Catch a duplicate ticket number
  5. Highlight the top 3 products

The exercises

1. Highlight reps over $300 Medium

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.

AB
1RepRevenue
2Maya320
3Owen210
4Priya450
5Lucas150
6Farah380

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.

2. Flag low stock visually Medium

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.

AB
1ProductUnits Left
2Mugs4
3Notebooks22
4Stickers8
5Pens15
6Folders11

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.

3. Flag the "needs review" score band Medium

Scores from 60 to 75 (inclusive) fall into a "needs review" band: not a clear pass, not a clear fail. Highlight exactly those.

AB
1StudentScore
2Lea92
3Noah68
4Ines55
5Marco74
6Priya61

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.

4. Catch a duplicate ticket number Medium

A guest list may have an accidental double-entry. Highlight every ticket number that appears more than once in the list.

AB
1GuestTicket #
2Aya4471
3Ben5820
4Cleo4471
5Dax3390
6Eli5820

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.

5. Highlight the top 3 products Hard

Highlight this quarter's 3 highest-grossing products, not a fixed dollar cutoff, whichever three happen to be on top.

AB
1ProductRevenue
2Widget400
3Gadget950
4Gizmo300
5Doohickey700
6Thingamajig500

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 →

More data tools practice

All Excel practice exercises · Excel test prep