↑ Return to Conditional Formatting

Top / Bottom

Highest / Lowest value

 

It’s quite easy with the conditional formatting to highlight the highest and lowest value of your serie.

  1. Select the range of cells where you have your data
  2. Select the menu Conditionnal Formating > Top/Bottom rules > Top 10 items

 

 

 

 

 
In the dialog box, replace 10 by 1 and choose a format.

 

 

 

 

 

When you close the dialog box, instantly the highest value has a different format of the rest of your range of cells.
You can customize your format by selecting Custom in the drop list

 

 

 

 

 

 

For highlighting the lowest value, the process is exactly the same as for the highest value but this time, select the menu Conditionnal Formating > Top/Bottom rules > Bottom 10 items

Test these rules in the following document by changing some value for courses:

 

 

 

 

 

 

 

 

Top / Bottom 10 values

To highlight the Top / Bottom 10 values of your data, you just have to use the same menu and let the parameter 10 in the dialog box. Here the question is not to activate the tool but how to use it consciously.

For instance, it is no necessary to apply the parameter 10 for the column Average because you have more than 10 cells. But for your range B2:G9, that’s more appropriate.

  1. Select the range of cells B2:G9
  2. Select the menu Conditionnal Formating > Top/Bottom rules > Top 10 items

 

 

 

 

 
In the dialog box, let the value 10.

 

 

 

 
When you close the dialog box, instantly the top 10 values has a different format of the rest of your range of cells.

Do the same for the 10 bottom and you have the following result:

 

 

 

 

 

 

 

 

 

 

Top / Bottom 10 percentage

The method to highlight the Top / Bottom 10 percent of your data, is exactly like for the value but in this case you select this menu.

As you can see, the number of cells where the format is different is less than for the top/bottom 10 value. Here only the value between 18 and 20 (10% of your population) is highlighted. So if only one cell respect this rule, only this cell will be highlighted.

Add/Remove some data in this document to see how the conditional formatting works with the percentage

Leave a Reply

%d bloggers like this: