Highlight the Top or Bottom values can be automatically done with the conditional formatting
- Specify the Top or Bottom values
You can set the number of values you want to highlight
- Specify the percentage of Top or Bottom values
Without a formula, Excel calculates the distribution of your data and applies the color accordingly
Highlight the highest value
To highlight the top 5 values in the following document, we will apply the following steps
- Select the range of cells where you have your data (B2:F8)
- Select the menu Conditional Formatting > Top/Bottom rules > Top 10 items
- In the next dialog box, you fill in the number of cells you want to highlight; here we fill the value 5.
- Also, you select the format you want to apply.
And immediately, the top 5 values of your document are highlighted.
What happens 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 that we have created the rule for the range B2:F8. So, obviously, the values of the column G are not impacted by the rule.
In this situation, we will update the rule
- Go to the 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.
- Then, you change the range of cells to include column G ($B$2:$G$8)
- Finish by clicking on Ok and then, the rule is applied to all your cells of the document 👍
Highlight the Top / Bottom percentage
This formatting rule can be misinterpreted by 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
In this situation, only 4 cells are highlighted. This is because these 4 cells represent the first 10%, in value of the cells. This is also known as the centile in statistics.
