Highlight Top / Bottom values with the conditional formatting

Highlight Top / Bottom values with the conditional formatting
Last Updated on 02/12/2023
Reading time: 3 minutes

Highlight the Top or Bottom values can be automatically done with the conditional formatting

  1. Specify the Top or Bottom values

    You can set the number of values you want to highlight

  2. 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

Which values are in the top 5 values

To highlight the top 5 values in the following document, we will apply the following steps

  1. Select the range of cells where you have your data (B2:F8)
  2. Select the menu Conditional Formatting > Top/Bottom rules > Top 10 items
Menu conditional formatting Top Bottom
  1. In the next dialog box, you fill in the number of cells you want to highlight; here we fill the value 5.
  2. Also, you select the format you want to apply.
Dialog box to set the number of top values

And immediately, the top 5 values of your document are highlighted.

Highlight the top 5 values

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?

New values are not included in the conditional rule

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

  1. Go to the menu Conditional Formatting > Manage Rules
Menu conditional formatting Manage rules
  1. 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 conditional formatting rules in a workbook
  1. Then, you change the range of cells to include column G ($B$2:$G$8)
New range to apply the rule of the conditional formatting
  1. 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

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

Highlight the cells representing the 10 top percent of the selection

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.

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Highlight Top / Bottom values with the conditional formatting

Reading time: 3 minutes
Last Updated on 02/12/2023

Highlight the Top or Bottom values can be automatically done with the conditional formatting

  1. Specify the Top or Bottom values

    You can set the number of values you want to highlight

  2. 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

Which values are in the top 5 values

To highlight the top 5 values in the following document, we will apply the following steps

  1. Select the range of cells where you have your data (B2:F8)
  2. Select the menu Conditional Formatting > Top/Bottom rules > Top 10 items
Menu conditional formatting Top Bottom
  1. In the next dialog box, you fill in the number of cells you want to highlight; here we fill the value 5.
  2. Also, you select the format you want to apply.
Dialog box to set the number of top values

And immediately, the top 5 values of your document are highlighted.

Highlight the top 5 values

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?

New values are not included in the conditional rule

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

  1. Go to the menu Conditional Formatting > Manage Rules
Menu conditional formatting Manage rules
  1. 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 conditional formatting rules in a workbook
  1. Then, you change the range of cells to include column G ($B$2:$G$8)
New range to apply the rule of the conditional formatting
  1. 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

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

Highlight the cells representing the 10 top percent of the selection

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.

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *