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
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.And the three who need help? With
B2:B11selected, add Bottom items, 3, in Red.Now the deals. Select
C2:C11, choose Above average, and highlight in Blue.Finn has just closed a big contract. Change his sales in
B7to 23000 and watch the green.
Remember
- Top and Bottom items mark the largest and smallest values of the selection.
- Above and below average work out the average for you.
- Select exactly the cells you mean: no heading, no total row.
- Nothing is sorted. The marks move when the data changes.
Previous: Find duplicates at a glance · Next: Data bars and colour scales