When 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 the top 10 percent in Excel.
How to Calculate Top 10 Percent in Excel: 4 Easy Ways
This article will demonstrate how to calculate the top 10 percent in Excel by using Conditional formatting, the PERCENTILE.EXC function, combining the RANK and COUNTIF functions and nesting the INDEX, MATCH, and ROW functions.
1. Using Conditional Formatting to Calculate and Highlight Top 10 Percent of Values in Excel
Here, we will calculate and highlight the top 10 percent of values in Excel by applying Conditional Formatting from the Home tab.
Steps:
- Let’s have a dataset of salespersons with their ID, Name, and Sales. Now we will calculate and highlight the top 10% of sales from the given dataset.
- Firstly, select the sales that you want to format.
- Then, go to the Home tab.
- After that, click on Conditional Formatting under the Home tab.
- Besides, choose the Top/Bottom Rules command.
- And then, click on the Top 10 % option.
- 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
- Now press the OK.
- Finally, you will see how the format of the sales column will be changed in the below image.
Read More: Ranking Data in Excel with Sorting
2. Calculate Top 10 percent of Values by Utilizing PERCENTILE.EXC Function in Excel
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
Syntax
=PERCENTILE (array, k)Arguments Explanation
| Argument | Required or Optional | Value |
|---|---|---|
| array | Required | Pass the data values. |
| k | Required | Pass the number which will be representing the kth percentile. |
Steps:
- Firstly, select the E5 cell.
- Secondly, write down the following formula.
=D5>=PERCENTILE.EXC($D$5:$D$24,0.9)- Thirdly, press Enter.
- 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 the top 10%.
- =D5> with this we are comparing the top 10% percent with the data of the previous column.
- So, you will see the result for the E5 cell.
- Then, use the Fill Handle tool and drag it down from the E5 cell to the E24 cell.
- Lastly, you will get all the results in the below image.
- Therefore, select the entire data set.
- Now, If you want to filter the data just click on the Filter icon under the Data tab
- After that, click on the Top 10% Filter icon.
- Now click on the Top 10% column and select only True then press the OK button
- Lastly, you can see here only the top 10% of sales data.
3. Combining RANK and COUNTIF Functions to Calculate Top 10 Percent of Values in Excel
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 the sales percentage (%).
Steps:
- Firstly, choose the E5 cell.
- Then write down the following formula.
=RANK(D5,$D$5:$D$24)+COUNTIF($D$5:D5,D5)-1- Then, press Enter.
- RANK(D5,$D$5:$D$24) will return the rank of each sales percentage.
- COUNTIF($D$5:D5, D5)-1 Here, the COUNTIF function will return the total count from the given dataset.
- So, you will see the Rank for the E5 cell.
- Then, use the Fill Handle tool and drag it down from the E5 cell to the E24 cell.
- As a result, you will find all the Ranks here in the below image.
Read More: Excel Formula to Rank with Duplicates
4. Nesting INDEX, MATCH and ROW Functions to Show All the Data of Top 10 Percent of Values
Now we will find out all the data about the top 10 percentages, like their ID, Name, Sales, etc. Let’s see how to do this.
Steps:
- Firstly, choose the I6 cell.
- Then write down the following formula.
=INDEX($D$4:$D$24,MATCH(ROW(1:1),$E$4:$E$24,0))- After that, press Enter.
- 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 an exact match.
- Lastly, the INDEX function will figure out the data of the matched cell.
- Then, you will see the sales for the I6 cell.
- After that, use the Fill Handle tool and drag it down from the I6 cell to the I15Â cell.
- Consequently, you will find all the sales here in the below image.
Read More: Ranking Based on Multiple Criteria in Excel
Things to Remember
- In the 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.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
Conclusion
In this article, we’ve covered 4 easy ways to calculate the top 10 percent in Excel. We sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
