How to Highlight Data Based on Percentile Rank in Excel

In this article, we will learn how to use Excel formulas to highlight data based on percentile rank. Percentile rank is a measure of how well a value compares to other values in a data set. For example, if a student scored 90 out of 100 in a test, and his percentile rank is 95, it means that he scored better than 95% of the students who took the test.

Highlighting data based on percentile rank can help us identify the outliers, the best performers, or the worst performers in a data set. For example, we can highlight the top 10% or the bottom 10% of the sales data to see which products or regions are doing well or poorly.

To highlight data based on percentile rank, we need to use two Excel functions: PERCENTRANK.INC and PERCENTRANK.EXC.

The PERCENTRANK.INC function returns the percentile rank of a value in a data set, as a percentage between 0 and 1, inclusive. The syntax of the function is:

=PERCENTRANK.INC(array,x,[significance])

Where:

• array is the range of data that you want to calculate the percentile rank for.
• x is the value that you want to find the percentile rank of.
• [significance] is an optional argument that specifies the number of significant digits to use in the calculation. The default value is 3.

The PERCENTRANK.EXC function returns the percentile rank of a value in a data set, as a percentage between 0 and 1, exclusive. The syntax of the function is:

=PERCENTRANK.EXC(array,x,[significance])

Where:

• array is the range of data that you want to calculate the percentile rank for.
• x is the value that you want to find the percentile rank of.
• [significance] is an optional argument that specifies the number of significant digits to use in the calculation. The default value is 3.

The difference between the two functions is that PERCENTRANK.INC includes the first and last values in the data set in the calculation, while PERCENTRANK.EXC excludes them. This means that PERCENTRANK.INC can return 0 or 1 as the percentile rank, while PERCENTRANK.EXC can never return 0 or 1.

Procedures

To highlight data based on percentile rank, we need to follow these steps:

1. Select the data that you want to highlight.
2. Go to the Home tab, and click on Conditional Formatting > New Rule.
3. In the New Formatting Rule dialog box, select “Use a formula to determine which cells to format”.
4. In the Format values where this formula is true box, enter the formula that defines the condition for highlighting. For example, if you want to highlight the values that are in the top 10% of the data set, you can use the formula =PERCENTRANK.INC($A$2:$A$11,A2)>=0.9, where $A$2:$A$11 is the data range, and A2 is the first cell in the data range. You can adjust the formula according to your data and criteria.
5. Click on the Format button, and choose the formatting style that you want to apply to the highlighted cells. For example, you can choose a fill color, a font color, a border, etc.
6. Click OK to close the Format Cells dialog box, and then click OK again to close the New Formatting Rule dialog box.
7. You should see the data that meets the condition highlighted with the chosen formatting style.

Example

Let’s see an example of how to highlight data based on percentile rank in Excel. Suppose we have the following sales data for 10 products in a table:

We want to highlight the products that have sales in the top 10% and the bottom 10% of the data set. To do this, we can use the following formulas:

• For the top 10%, we can use =PERCENTRANK.INC($B$2:$B$11,B2)>=0.9
• For the bottom 10%, we can use =PERCENTRANK.INC($B$2:$B$11,B2)<=0.1

We can apply these formulas as conditional formatting rules to the data range, and choose different formatting styles for each rule. For example, we can choose green fill color for the top 10%, and red fill color for the bottom 10%. The result will look like this:

We can see that products I and J have sales in the top 10% of the data set, and products A and B have sales in the bottom 10% of the data set. They are highlighted with green and red colors, respectively.

Other Approaches

There are other ways to highlight data based on percentile rank in Excel, such as using the PERCENTILE.INC or PERCENTILE.EXC functions, or using the built-in conditional formatting options. Here are some examples of how to use these approaches:

• Using the PERCENTILE.INC or PERCENTILE.EXC functions: These functions return the value at a given percentile in a data set. For example, =PERCENTILE.INC($B$2:$B$11,0.9) returns the value at the 90th percentile in the sales data, which is 400. We can use these functions to compare the values in the data set with the percentile values, and highlight them accordingly. For example, if we want to highlight the values that are above the 90th percentile, we can use the formula =B2>PERCENTILE.INC($B$2:$B$11,0.9). Similarly, if we want to highlight the values that are below the 10th percentile, we can use the formula =B2<PERCENTILE.INC($B$2:$B$11,0.1).
• Using the built-in conditional formatting options: Excel has some predefined conditional formatting options that can help us highlight data based on percentile rank. For example, we can use the Top/Bottom Rules option to highlight the top or bottom 10% of the data. To do this, we can select the data range, go to the Home tab, and click on Conditional Formatting > Top/Bottom Rules > Top 10% or Bottom 10%. We can then choose the formatting style that we want to apply to the highlighted cells.