Percentile Rank Formula

percentile rank in excel classification

Learn how to use the percentile rank formula to classify data based on criteria.

We often use percentile ranks in data analysis. This feature allows us to check the group’s overall target performance.

Highlight Data Based on Percentile rank

The problem

We have a list of 20 products with different sales. So, instead of the top n% and bottom n%, we want to classify the products based on sales.

The solution

We’ll use a custom conditional formatting formula that contains the formulas based on the PERCENTILE function. Using the function is not rocket science; we must add only two arguments: the range containing data and the percentile score.

In the example shown, we split the range into five parts based on requirements. Next, we’ll apply different colors using the following rules.

=B2>=PERCENTILE($B$2:$B$16,0.85) 'Highlight the top 15%
=AND(B2<PERCENTILE($B$2:$B$16,0.85),B2>PERCENTILE($B$2:$B$16,0.66)) 'Between 66% and 85%
=AND(B2<PERCENTILE($B$2:$B$16,0.66),B2>PERCENTILE($B$2:$B$16,0.33)) 'Between 33% and 66%
=AND(B2<PERCENTILE($B$2:$B$16,0.33),B2>PERCENTILE($B$2:$B$16,0.20)) 'Between 20% and 33%
=B2<=PERCENTILE($B$2:$B$16,0.2) 'to highlight the bottom 20% value

It is worth using the AND function to find a range between X and Y.

Steps to create a classification

  1. Select the range that contains data. (in the example, range B2:B16). Click the Home tab. Select Conditional Formatting and New Rule. A dialog box will appear.
Steps to create classification using percentile rank

2. Locate the “Use a Formula to Determine Which Cells to Format” option. All applied formulas will be evaluated based on the rule. The selected formatting style will highlight the range if the result is TRUE.

3. Now, enter the formula. In the example, we’ll find the top 15% values in a range.

=B2>=PERCENTILE($B$2:$B$16,0.85)

4. Click the Format button. The Format Cells dialog box will appear. You can customize the formatting style. Click OK, and we’ll return to the ‘New Formatting Rule dialog box’.

5. Click OK to validate the entered formatting rule.

6. At this point, you should be in the Conditional Formatting Rules Manager dialog box.

Repeat these steps to apply another four rules.

percentile rank in excel classification

Tip: If you need to change your scenario, select a single cell in a highlighted range. Click on the Home tab and select conditional formatting. Select the Rules Manager dialog box. Choose the rule you want to modify. Click the Edit rule button.

Additional resources