Learn how to calculate percentile in Excel in tried and tested ways.
Calculating percentiles in Excel can feel like grading on a curve in a university class. If you’ve ever had to rank students based on their performance, you know how tricky it can getβespecially when the data isnβt sorted or youβre dealing with multiple scores.
In Excel, percentiles show how a value stacks up against others in a dataset. If you havenβt used Excelβs functions or calculation tools before you might feel finding the right formula is a bit challenging task. Thatβs where I come inβIβll walk you through several ways to calculate percentiles for different distributions and scenarios, using real examples and simple, step-by-step instructions.
Calculate Percentile Manually
It’s quite easy to use Excel to calculate the percentile of the given nth position, like the 7th percentile of a dataset. However, learning the manual approach will give you a more foundation-level idea of this statistical tool. So, try this method first before moving on to the automated ones.

Go to the dataset, select the header row, and press Ctrl + Shift + L to activate the Sort & Filter tool.

Click on the Sort & Filter arrow of the numerical data column and select the Sort Smallest to Largest option.

Now, name a cell Median and enter the following formula into the adjacent blank cell. Press Enter to calculate.
=MEDIAN(B2:B12)
Don’t forget to change the cell range reference, B2:B12
, to match your dataset.
You get the median or Q2 of the source dataset.
Q2 is also the 50th percentile of the input data.

Now, divide the data into two halves at the median point as shown below:
- Lower half (below the median): 64, 72, 75, 78, 81
- Upper half (above the median): 87, 88, 90, 92, 95
You can do the above division by coloring the cells using the Fill Color tool of the Font block in the Home tab.

Name another cell Q1 and use this formula to calculate the median for the lower half data points:
==MEDIAN(B2:B7)

Then, rename another cell to Q3, and enter this formula to calculate the median for the upper half of the dataset:
=MEDIAN(B7:B12)
Do pause to modify the cell ranges mentioned in the above formulas according to your worksheet.
By calculating Q1 and Q3, you derived the 25th percentile and 75th percentile values in the given dataset.

Find above the 25th percentile, 50th percentile, and 75th percentile values for the dataset I used in this tutorial.
Using Excel’s Data Analysis Toolpak
The Excel Data Analysis Toolpak contains some of the most useful statistical tools including the Rank and Percentile function.
If you haven’t used this tool yet, you’ll need to activate it by following the Excel tutorial given below:
π Read More: How to Install Data Analysis Toolpak in Microsoft Excel

Once activated, go to the Data tab and click on the Data Analysis command inside the Analysis commands block.
The Data Analysis dialog box will open.
Scroll down on the list, select Rank and Percentile, and click OK to launch the tool.

Click into the Input Range field and select the numerical data you need to analyze.
The Grouped By field should be set to Column.
Checkmark the checkbox for the Labels in the first row.
Click on the Output Range field and select the destination cell on the active worksheet where you wish to populate the percentile values.
Click OK to calculate.

Excel will create a new data table containing a complete percentile analysis of the input dataset.

For example, the number of Mean Middle School Goers 10,000,000
in Brazil is 88th percentile in the sample dataset used to demonstrate the tool’s functioning.
Using the PERCENTILE.INC Function
The PERCENTILE.INC function in Excel calculates the value at a specific percentile within a data set. This function ensures that the smallest and largest values are included in the calculation. It helps you find a value below which a certain percentage of data falls.
It’s more often used in test score analysis. For instance, you can create a formula using this function to determine the score at a certain nth percentile, say 90th. Then, you can use it as a cut-off to admit or pass students.

Go to the source worksheet and name a cell as 90th Percentile. To its adjacent cell on the right, enter 0.90, which will be used as the input value for the k argument in the PERCENTILE.INC function.
Now, select the next blank cell on the right, type in the following formula into the cell, and press Enter:
=PERCENTILE.INC(B2:B12,E2)
You must adjust the cell range (B2:B12
) and k values (E2
) according to your own dataset.

Excel should calculate the 90th percentile instantly, which is 92
for the sample dataset used in this tutorial.
If you wish to calculate other percentile values, use the following k values instead:
- 25th percentile:
0.25
- 33rd percentile:
0.33
- 50th percentile:
0.50
- 66th percentile:
0.66
- 75th percentile:
0.75
Using the PERCENTILE.EXC Function
The PERCENTILE.EXC function in Excel calculates the value at a specific percentile but excludes the smallest and largest values from the data set. This function is useful when you want to find percentiles without including the absolute minimum and maximum points.
It helps to reduce the impact of outliers by excluding the smallest and largest values from the percentile calculation. This makes it useful when analyzing data sets where extreme values might distort the results. By focusing on the central data points, it provides a more accurate reflection of typical performance or trends.
For example, by calculating the 95th percentile during athlete performance analysis, you can measure top-performing athletes while ignoring extreme outliers that could skew results. Contrarily, in sales data analysis, it helps to find the 10th percentile to evaluate lower sales figures without including the smallest sales values.

Name a cell in your worksheet indicating the percentile value you’re calculating, like the 10th Percentile. To its adjacent cell on the right, enter 0.10.

Now, into the next cell in the same row, enter this formula:
=PERCENTILE.EXC(B2:B11,E2)
In the above formula, B2:B11
is the array argument and E2
is the k-value argument. Customize these cell range references in your worksheet before using the formula.

Hit Enter to calculate the 10th percentile value.
Using the PERCENTRANK Function
The PERCENTRANK is a legacy Excel function that allows you to calculate the percentile for each row in Excel. You might typically want to use this function when you want to compare each rowβs value to the entire dataset in a relative way.

In the above sample dataset, you wish to compare each country’s mean salary with the rest of the countries in the table expressed as nth percentile values. For example, you might want to know the percentile position of Canada compared to other countries in the table.
Create a Percentile column in the source dataset.

Select the first cell below the new column. Enter the following formula and press Enter to calculate the percentile.
=PERCENTRANK($B$2:$B$11,B2)*100
Don’t forget to change the source cell range $B$2:$B$11
and the compared value B2
in the above formula. Since you’ll be comparing each value of the given dataset with the entire column, select the cell range and press F4 to convert the variable range to a fixed range.
Now you get the percentile position for the first cell.

Select the cell and drag the fill handle down the column to apply the formula to the rest of the column.

Excel will instantly generate the percentile values by each row.
π Read more: If you’ve found this Excel tutorial on statistics insightful, you’ll also want to go through these:
Conclusions
So, now you know how to calculate percentile in Excel in different ways. You’ve learned a manual method suitable for you if you’ve just started to learn statistics. Contrarily, if you need to analyse percentile by rows in bulk, using can use the Data Analysis Toolpak in Excel.
You’ve also learned to use Excel functions, like PERCENTILE.INC, PERCENTILE.EXC, and PERCENTRANK to derive custom percentile values.
If you’ve liked this Excel tutorial or have feedback use the comment box to share your thoughts.
0 Comments