What’s the Percentile Function in Google Sheets?

Let’s talk about how to use the PERCENTILE function. This originated in Excel, and you’ll still find it there if you use an older version of that software. Newer versions use something called PERCENTILE.INC. There’s also a way to do the same thing with Google Sheets. Below, I’ll talk about each of these. 

Notably, we’ll foray into the world of data analysis with real-world examples. I’ll also show you how to use the PERCENTILE function in Google Sheets.

What is the Percentile Function?

This might sound academic, but the percentile formula provides the value below which a specified percentage of observations within a group falls. Does that make sense? If you’re in the 5th percentile, you’re in the lowest five percent of a specific audience. If you’re in the 95th percentile, you’re in the top five percent. We’ll get deeper into this later in my guide.

When you calculate the percentile of a range of data, you end up splitting your data into two portions. One portion is above the percentile value and the other portion is below it. When it comes to grading curves in exams, the percentile concept is used.

It is pretty useful and easy to learn. However, it should not be confused with percentage. Before we go deeper into the percentile function, let’s look at how it differs from a percentage.

Is This the Same as a Percentage?

No, the percentile is not the same as a percentage in Google Sheets.  A percentage represents a value out of 100. For example, 50% of 100 is 50, and 50% of 40 is 20. On the other hand, the percentile represents position or rank in data.

Let’s use a test for an example. Normally, if a student scores only 55% on an exam of 100 questions, this would be a failing score. That’s because the student only got slightly more than half of the questions correct. However, if the rest of the class scored only 30% or 40% correct out of 100 questions, then the student who scored 55% on the exam would actually be in the highest percentile on a grading curve. We will see this in action in a later example.

Now, let’s move onto how to use it in Excel and Google Sheets.

How To Use The Percentile Function in Google Sheets

Let’s go back to using a test score example. Let’s say you are a teacher and it is time to compile the final exam scores. The first thing you would do is create a simple list with student names and scores. Note that my example here shows the data set in Google Sheets.

As you can see, our student names are in cells A3 to A13, and their corresponding scores are in cells B3 through B13.

Now it is time to use the Google Sheets percentile formula:

=PERCENTILE(Data Start:Data End, Percentile )

That’s different in Excel. The most recent versions of the software would use the PERCENTILE.INC function:

=PERCENTILE.INC(Data Array,k)

Note that “Percentile” and “k” are both the same thing here. You’re just listing out which percentile you want to target with the formula. For example, we’re going to look at the 50th percentile of the test scores in our data set.

To do that, I first click on cell E3. (Remember, I’m using Google Sheets for this example.)

In Cell E3, right next to where I’ve listed “50th Percentile,” I enter the following formula.

=PERCENTILE(B6:B20,0.5)

Note that you’ll want to use the numeral 0.5 here. That’s what shows the 50th percentile. 

You’ll notice that it comes out to 78. Scores of 78 and below are in the 50th percentile for this data set. That means that anyone with a score of 78 or lower, such as Cole, Corey, and Corinne, is in the lower 50th percentile of test scores. However, anyone with 79 or higher, such as Bernice, is in the upper 50th percentile of test scores.

You might notice some extra color in my data set too. I used conditional formatting in Google Sheets to achieve that effect.

Note that I calculated the 75th and 95th percentile of this data, too. It’s the same formula. You just update the “Percentile” value I discussed above. 

Here’s what it looks like for the 75th percentile:

=PERCENTILE(B6:B20,0.75)

And here’s what it looks like for the 95th:

=PERCENTILE(B6:B20,0.5)

You’ll notice from my photo above that these produce the results of 91 and 94.3, respectively. You can use this type of information in a lot of ways, but it’s very commonly used for test scores. I compiled some fantastic Google Docs templates for teachers, too.

Percentiles in the Latest Version of Excel

The latest version of Excel removed the original PERCENTILE function because it was too imprecise. Now, it’s replaced by inclusive and exclusive percentile functions. The inclusive function is called with PERCENTILE.INC. 

The syntax of the Percentile function in Excel looks like this for the inclusive version:

=PERCENTILE.INC(array,k)

You can call the exclusive percentile function with PERCENTILE.EXC. Here’s what it looks like when you break it down.

=PERCENTILE.EXC(array,k)

In most cases, you’ll likely be using the inclusive percentile function. That said, you’ll want to understand the exclusive function as well.

Remember, the “array” in Excel is similar to the way you’d enter it in Google Sheets. Just enter the data starting point, a colon, and the data ending point. K is just the percentile.

Conclusion

Well, there we have it! You now understand how to use the percentile Google Sheets function. As you can see, this is a very simple and useful function that you can use when you want to calculate the ranking of specific numbers within a data range. 

I hope you found this tutorial helpful and can take more comfort in analyzing future data.

Looking for more information? I also discussed how to transpose data. As always, I include specific, real-world examples with all of my guides. I also try to show videos with exactly how to replicate my methods. Here’s one on how to make charts in Google Sheets.