It's quite easy with the conditional formatting tool to highlight the highest and lowest values of your data AUTOMATICALLY.
Highlight the highest value
This conditional formatting option is very useful to visualise which values are the highest in our document.
- Select the range of cells where you have your data (B2:F8)
- Select the menu Conditionnal Formating > Top/Bottom rules > Top 10 items
In the next dialog box, you fill the number of cells you want to highlight. And also the format you want to apply.
And immediately, the top 5 cells of your document have a specific format.
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?
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.
In the next dialog box, it's important to select This Worksheet to display all the rules of your document.
Then, you change the range of cells to include the column G ($B$2:$G$8)
Finish by clicking on Ok and then, the rule is applied to all your cells 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
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.
