How to Calculate the Top 10 Percent in Excel (4 Ways)

Output of the conditional formatting

At the time of working with numbers, we may need to find out or calculate the top 5, 10, or any specific percent in Excel. Generally, Excel provides various methods for this type of purpose. In this article, I will show different ways to Calculate Top 10 Percent in Excel.

Download the Practice Workbook

4 Ways to Calculate Top 10 Percent in Excel

1. Calculate and Highlight Top 10% of Values Using Conditional Formatting

Let’s have a dataset of salespersons with their ID, Name, and Sales. Now we will calculate and highlight the top 10% sales from the given dataset.

 Calculate and Highlight Top 10% of Values Using Conditional Formatting

Step 1: Select the sales that you want to format

 Select the cells

Step 2: Click on Conditional Formatting under the Home tab

Open conditional Formatting

Step 2: First click on the Top/Bottom Rules and then Top 10%

Select top 10%

Step 3: Now a pop-up window will be opened. Select the percentage option as per your need. And there is a list of colors. Choose your colors as per your need

Select color and percentage for conditional formatting

Step 4: Now press the Ok button

Click on OK button

Step 5: Now the format of the sales column will be changed

 Output of the conditional formatting

3. Calculate Top 10% of Values Using a Formula

Now let’s see how we can calculate the top 10% using the formula. In the formula, we will use the PERCENTILE function. Let’s see the fundamentals of this function

=PERCENTILE (array, k)

Arguments

Argument Required or Optional Value
array Required Pass the data values.
k Required Pass the number which will be representing the kth percentile.

Step 1: Enter the formula in cell E5 and copy it down up to E24

=D5>=PERCENTILE.EXC($D$5:$D$24,0.9)

Formula Explanation

  • PERCENTILE.EXC($D$5:$D$24,0.9) here $D$5:$D$24 is the array where we will find the data. 0.9 is used as we want to get top 10%. To more about PERCENTILE function you can check this link
  • =D5> with this we are comparing the top 10% percent with the data of the previous column.

Enter formula using percentile

Step 2: If you want to filter the data just click on the Filter icon under the Data tab

 click on filter icon

Step 3: Now click on the Top 10% column and select only True then press the OK button

 Select TRUE

Step 4: Now only the top 10% of sales data will be shown only

 output of the filtered data

Read More: Excel Percentile Rank Examples (7 Quick Ways)

3. Calculate Top 10 Percentage Using RANK and COUNTIF Functions

Now we will find the top 10 percentages according to their rank. Here in the sales column instead of having the total sales, we will use sales percentage (%).

 Calculate Top 10 Percentage Using RANK and COUNTIF Functions

Step 1: Enter the formula in cell E5 and copy it down up to F24

=RANK(D5,$D$5:$D$24)+COUNTIF($D$5:D5,D5)-1

Formula Explanation

Enter formula using Rank and Countif functions

Read More: Rank IF Formula in Excel (5 Examples)

4. Show All the Data of the Top 10 Percentage

Now we will find out all the data of the top 10 percentages like their ID, Name, sales, etc. Let’s see how to do this.

 Show All the Data of the Top 10 Percentage

Step 1: Enter the following formulas into three different columns

For column G

=INDEX($B$4:$B$24,MATCH(ROW(1:1),$E$4:$E$24,0))

For Column H

=INDEX($C$4:$C$24,MATCH(ROW(1:1),$E$4:$E$24,0))

For Column I

=INDEX($D$4:$D$24,MATCH(ROW(1:1),$E$4:$E$24,0))

Formula Explanation

Enter formula using INDEX MATCH and ROW function

Things to Remember

  • In PERCENTILE function, k can be provided as a decimal (.7) or a percentage (70%)
  • Also, the k must be between 0 and 1, otherwise, PERCENTILE will return the #NUM! Error.

Conclusion

These are the ways to calculate the top 10 percent in Excel. I have shown all the methods with their respective examples but there can be much other iteration. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us.


Related Articles

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo