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.

**Table of Contents**hide

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