Md. Abdullah Al Murad
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.
Step 1: Select the sales that you want to format
Step 2: Click on Conditional Formatting under the Home tab
Step 2: First click on the Top/Bottom Rules and then 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
Step 4: Now press the Ok button
Step 5: Now the format of the sales column will be changed
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.
Step 2: If you want to filter the data just click on the Filter icon under the Data tab
Step 3: Now click on the Top 10% column and select only True then press the OK button
Step 4: Now only the top 10% of sales data will be shown only
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 (%).
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
- RANK(D5,$D$5:$D$24) this will return the rank of each sales percentage.
- COUNTIF($D$5:D5, D5)-1 this part will return the total count from the given dataset. If you want to explore more about the COUNTIF function you can check these links:
COUNTIF Multiple Ranges Same Criteria in Excel
COUNTIF between Two Cell Values in Excel (5 Examples)
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.
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
- MATCH(ROW(1:1),$E$4:$E$24,0) this part will match the rows with the rank data and as we have declared 0 as the third argument, it will consider exact match.
- Lastly, the INDEX function will figure out the data of the matched cell.
- If you want to explore more about this INDEX, MATCH, and ROW function you can check this link:
How to Use INDEX Function in Excel (8 Examples)
How to use MATCH function in Excel (3 Examples)
How to Use ROW Function in Excel (With 8 Examples)
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.
