Course / Conditional formatting

Top, bottom and above average

Mark the best three, the worst three and everyone above average, without sorting anything. Beginner · 4 min · Top items, Bottom items, Above average

Some rules don't compare a cell with a fixed number. They compare it with the rest of the selection: the three largest, the three smallest, everything above the average.

That makes the selection part of the rule. Top 3 of column B means the three best in column B, so select exactly the cells you mean, without the heading or a total row.

The list stays in its own order. Nothing is sorted: the leaders are marked where they stand.

In Excel, these are under Conditional Formatting → Top/Bottom Rules. The first is called Top 10 Items, and you change the 10 to whatever you need.

Steps

  1. Who are the three best sellers? Select B2:B11, click Conditional formatting in the toolbar, choose Top items, set How many to 3, and highlight in Green.

  2. And the three who need help? With B2:B11 selected, add Bottom items, 3, in Red.

  3. Now the deals. Select C2:C11, choose Above average, and highlight in Blue.

  4. Finn has just closed a big contract. Change his sales in B7 to 23000 and watch the green.

Remember

Previous: Find duplicates at a glance · Next: Data bars and colour scales