Conditional Formatting – Highlight highest values

It's quite easy with the conditional formatting tool to highlight the highest and lowest values of your data AUTOMATICALLY.

Change the color automatically

Highlight the highest value

This conditional formatting option is very useful to visualise which values are the highest in our document.

Which values are the 5 highest sales
  1. Select the range of cells where you have your data (B2:F8)
  2. Select the menu Conditionnal Formating > Top/Bottom rules > Top 10 items
Menu conditional formatting Top Bottom

In the next dialog box, you fill the number of cells you want to highlight. And also the format you want to apply.

Dialog box to set the number of top values

And immediately, the top 5 cells of your document have a specific format.

Highligh the top 5 values

What's happen with new data?

Now, we will add data in column G. But the new values aren't included in the conditional rule. Why?

New values are not included in the conditional rule

The reason is because when we have created the rule, we have selected the range B2:F8. So, obviously, the new data aren't in the range of the conditional formatting

Change the range of the rule

In this situation, it isn't necessary to recreate a new rule. We just need to change the range of application of the rule.

Menu conditional formatting Manage rules

In the next dialog box, it's important to select This Worksheet to display all the rules of your document.

Option to display all the rules in a workbook

Then, you change the range of cells to include the column G ($B$2:$G$8)

New range to apply the rule of the conditional formatting

Finish by clicking on Ok and then, the rule is applied to all your cells of the document.

New top 5 values of the document

Top 10%

This formatting rule can be misinterpreted for someone who did not construct the document.

In this example, when the option Conditional Formatting > Top/Bottom rules > Top 10% is selected, the result is the following

Highlight the cells representing the 10 top percent of the selection

Only 4 cells are highlighted. This is because these 4 cells represents the first 10%, in value of the cells. This is also known as the centile in statistics.

Related posts


Permanent link to this article: https://www.excel-exercise.com/top-bottom/

Leave a Reply

Your email address will not be published.