# How to Calculate the Top 10 Percent in Excel (4 Ways) 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.

## 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 ### 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 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 ## 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  