In this article:

May 8, 2024

The percentile in Google Sheets is a statistical function that determines the value below which a specific percentage of data in a dataset falls. The percentile function is used to understand the distribution of data in a dataset.

**Example: **For instance, if you have a list of test scores and you want to find the score below which 30% of the scores fall, you would use the percentile function to calculate this.

This function is particularly useful in data analysis for understanding how data is spread out and for identifying outliers or specific data trends.

The basic syntax for the percentile function in Google Sheets is

**PERCENTILE(data, percentile), **

where:

**Data:**This is the range or array of data for which you want to calculate the percentile.

**Percentile:**This is the percentile you wish to calculate, expressed as a number between 0 and 1. For example, if you want to find the 30th percentile, you would use 0.3.

This function will return the value at the given percentile within your data set. For example, PERCENTILE(A1:A100, 0.3) will calculate the 30th percentile of the values in the range A1 to A100.

Follow the simple steps below to use the percentile formula in Google Sheets.

Open Google Sheets and create a new spreadsheet. In column A, input names or identifiers, and in column B, enter the corresponding data values.

Choose an empty cell for the percentile result. Type the formula =PERCENTILE(B:B, followed by the desired percentile as a decimal, then close the formula and press Enter. For example, click on cell C2, type ‘=PERCENTILE(B:B, 0.3)’ to calculate the 30th percentile of scores in column B, and press Enter.

The selected cell will now display the calculated percentile value. In our example, cell C2 shows the score below which 30% of the students' scores fall.

Modify the percentile number in the formula to explore various aspects of your data.

You can change the formula in C3 to =PERCENTILE(B:B, 0.5) to find the median (50th percentile).

Use the same steps for percentile calculations on any other relevant data set in your spreadsheet.

We hope that you now have a better understanding of what a percentile in Google Sheets is how to use a percentile in Google Sheets. If you enjoyed this article, you might also like our article on how to set up the percent formula in Google Sheets or our article on how to do a t-test in Google Sheets.

Get Google Sheets productivity and automation tips delivered straight to your inbox

We'll email you 1-3 times a week — and never share your information.

Get your copy of our free Google Sheets automation guide!

- 27 pages of Google Sheets tips and tricks to save time
- Covers pivot tables and other advanced topics
- 100% free

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->