How to Calculate Percentiles in Excel Fast (Formula)

We’ll have seen our result cards with a certain percentile mentioned on it (good or bad, besides the point 😝)

Often people confuse percentiles for percentages, and others do not know the concept of percentiles.

This tutorial is a step-by-step guide on what percentiles are and how can you quickly calculate them in Microsoft Excel.

Get your free practice workbook for this guide here and continue reading until the end to learn all about percentile calculation in Excel.

Table of Contents

What is Percentile?

A percentile is a statistical measure that indicates the position of value in a dataset 🥇

It is expressed as an n-th value like the 90th percentile and indicates the data points that are equal to or below that n-value in a dataset.

It is massively used in fields such as finance, healthcare, and education. But honestly, I have mostly seen it on my result cards (not in a very positive sense though).

For example, my report cards would often say that I fall in the 60th percentile on a test. Today I know it meant that I scored the same as or better than 60% of students who took that test.

In simpler terms, if 10 students from my class took a test, falling in the 60th percentile means I performed the same as or better than 6 students ✌

Percentiles are a great way to provide insights into the relative position of a data point within a dataset. My 60th percentile tells where I stand among the students in my class.

How to Calculate Percentiles in Excel?

It’s time we delve into a practical example of calculating percentiles in Excel.

So here I have a set of scores of students from a recent test. Based on these scores, I want to find certain percentiles for it 👇

Pro Tip!

To calculate percentiles in Excel, we have three percentiles function.

PERCENTILE

The PERCENTILE function is the function from the older versions of MS Excel. It is only retained for backward compatibility purposes and is not recommended for use unless you are subscribed to Excel 2010 and older versions.

PERCENTILE.INC & PERCENTILE.EXC

The PERCENTILE.INC and PERCENTILE.EXC functions are the upgraded versions of the PERCENTILE function available in Excel 2010 and newer versions.

Both these functions calculate the n-th percentile for a given dataset which will be somewhere between 0 and 1.

However, the only difference between both these functions is that the n-th percentile calculated by the PERCENTILE.INC ranges between 0 and 1 (inclusive).

Whereas that calculated by the PERCENTILE.EXC function ranges between 0 and 1 (exclusive).

It is recommended to use the PERCENTILE.INC function unless you prefer exclusivity of 0 and 1 in your results.

Using the PERCENTILE.INC function

We will use the PERCENTILE.INC function to calculate the percentiles for this dataset. Let’s go then.

Step 1) Begin writing the PERCENTILE.INC function as below.

The first argument is the array function where we will refer to the dataset from which the percentile is to be found 📑

Step 2) As the array argument, refer to the array of student scores as below.

The second argument is k. This refers to the nth percentile that you want to find.

So, let’s say we want to find the 90th percentile for this dataset.

There are two ways how you can supply 90 as k to this function.

• Either as 0.9
• Or as 90%

This is because the Percentile functions of Excel only accept the k values as a number between 0 and 1.

Step 3) As the k argument, write 0.9.

Step 4) Hit enter to get the 90th percentile for these scores.

Comes out as 92.6. This means that any student who scored 92.6 or more has done better than 90% of the students on this test 😲

Step 5) To calculate the 70th percentile for these scores, change the k value in the formula above to 0.7.

Step 6) Similarly, I am calculating multiple percentiles for this data.

Easy? That’s how you can calculate any percentile in Excel.

Using the PERCENTILE.EXC function

Are you wondering what would have happened if we’d calculated the same percentiles through the PERCENTILE.EXC function?

The results would have been very different for most percentiles. And for some of them, Excel would have returned the #NUM! error 🚫

Also, if calculated in the 0th or the 100th percentile, the PERCENTILE.EXC function would have returned the #NUM! error.

This is because of the PERCENTILE.EXC function only accepts k values that are within the range 0 and 1 (exclusive of 0 and 1 itself).

Whereas the PERCENTILE.INC function will accept 0 and 1 as k values, too.

In fact, not only 0 and 1, but the PERCENTILE.EXC function would also return the #NUM! error if the k value is anywhere between:

• 0 to 1/(N+1) or
• N/(N+1) to 1

For our dataset, this is:

• 0 to 1/8 > 0 to 0.125
• 7/8 to 1 > 0.875 to 1

See here, I supplied the k argument as 0.123 and got the #NUM! error.

Same with the k value set to 0.88 (greater than 0.875)

However, only if I slightly tweak the formula for the PERCENTILE.EXC function, the results for both the functions would tally (to the exclusion of the k-values for which we have the #NUM! error) 💭

Step 1) Select the range of cells containing the student scores.

Step 2) Go to the Home Tab > Editing > Sort & Filter > Sort Smallest to Largest.

This will arrange the data (student scores) in ascending.

Step 3) Write the following formula for PERCENTILE.EXC function.

Step 4) Drag it down against the entire list to see the results.

What have I done?

I have only changed the array reference from $A$2:$A$8 to $A$3:$A$7.

Since the data is arranged in an ascending order, the first and the last cell contain the smallest and the largest value that I have removed from the array.

This is exactly what the PERCENTILE.EXC function does 💡

It doesn’t account for the 0 and 1 k values (which are the smallest and largest data values from the dataset). It calculates percentiles by excluding them.

Whereas the PERCENTILE.INC calculates percentiles by including them.

Hence, when we calculate percentiles using the k value as 0 and 1 (0th and 100th percentile), the PERCENTILE.INC function returns the smallest and the largest value of the dataset.

Whereas the PERCENTILE.EXC returns the #NUM! error (since it never considered them a part of the dataset)

With this, you now know how to calculate percentiles in Excel. Any nth percentile and for any dataset.

Things to remember!

To make your percentile calculation venture in Excel all the easier and more hassle-free, here are some pointers for you to remember 🎯

Step 1) If the array is empty, the PERCENTILE.INC and PERCENTILE.EXC function will return the #NUM! error.

Step 2) If the k argument is anything but numeric values, the PERCENTILE.INC and PERCENTILE.EXC function will return the #VALUE! Error.

Step 3) If k<0 or if k>1, the PERCENTILE.INC will return the #NUM! Error.

Step 4) If k ≤ 0 or if k ≥ 1, the PERCENTILE.EXC will return the #NUM! Error.

Conclusion

We have discussed all the available percentile Excel functions that’ll help you calculate percentiles in Excel. One of the key tricks to calculate the correct percentile is to know how do you want the boundary cases (the minimum and the maximum values) of your dataset to be treated.

The PERCENTILE.EXC function is suitable when you want to eliminate the boundary cases or potential outliers from your dataset while calculating percentiles. Otherwise, use the PERCENTILE.INC function.

Hope that you understand the difference between them both and know which function suits you best. In addition to percentile calculation, some other very interesting statistical concepts from explained in my Excel tutorials include:

• How to Calculate Standard Deviation in Excel Step-by-Step (2024)
• How to do Linear Regression in Excel: Full Guide (2024)
• How to Calculate a Confidence Interval in Excel (2024)