In this article:

Percentile in Google Sheets: The Ultimate Guide in 2024

percentile google sheets

What is Percentile in Google Sheets?

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.

Percentile Function in Google Sheets

The basic syntax for the percentile function in Google Sheets is

PERCENTILE(data, percentile), 


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

How to Use the Percentile Formula in Google Sheets

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

1. Open Google Sheets and Enter Data

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

google sheets percentile

2. Select a Cell, Type and Execute the Percentile Formula

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.

percentile formula google sheets

3. Interpret the Result

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.

percentile function google sheets

4. Adjust Percentile for Different Analysis

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

percentile in google sheets

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

5. Apply to Various Data Sets

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.

Automate everything you track in spreadsheets with Lido
Learn more

Automate manual tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
Creates a one- or multiple-day calendar event with optional attendees
Adds an object to Hubspot
Create a Slack channel, and optionally add a topic or members
Makes any HTTP request
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
Makes any HTTP request and returns the response
Inserts given array below defined values in given worksheet
Sends an email using your Google account
Sends an email using your Microsoft Outlook account
Sends a Slack message
Sends an SMS message using your Twilio account
Updates cells with given values
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started