How to Find Quartiles in Google Sheets

The QUARTILE function is a useful way to find quartiles in Google Sheets given a range of values.

With the QUARTILE function, we can automatically get the quartiles in your dataset without manually ordering the values.

The rules for using the QUARTILE function in Google Sheets are as follows:

• The function takes a range of numbers as well as the quartile needed.
• The function returns the value nearest to the specified quartile.

Let’s take a look at an example.

A professor just finished computing the results of an exam for her English class. She promised to give the top 25% of her class bonus points in the final paper. Aside from that, she also wants to know if the bottom 25% are not performing well enough. 

Luckily, she already made a Google Sheet tracker of all the scores. With the QUARTILE function, she finds out that the quartiles are 52, 72, 85, 92, 99. The fourth number (Q3) indicates that anyone who scores above 92 is part of the top 25% and will get bonus points!

The first and second numbers (Q0 and Q1) tell her that the bottom 25% have scores between 52 to 72, well below the passing grade of 75%! She considers inviting these students to one-to-one consultations. Finally, the third number (Q2) tells her that half of the students scored 85 and higher on the exam!

This use case is just one way to apply the QUARTILE function in Google Sheets.  Quartiles are very useful when working with datasets that have outliers or are not symmetrically dispersed. It can provide you with more information than using just AVERAGE() or MEDIAN().

The QUARTILE function is a convenient way to get a sense of how your data points are distributed. 

Let’s learn how to find quartiles in Google Sheets and later use actual values and formulas to see the QUARTILE function in action.

The Anatomy of the QUARTILE Function

So the syntax (the way we write) the QUARTILE function is as follows:

=QUARTILE(data, quartile_number)

Let’s dissect this thing and understand what each of these terms means:

• ‘=‘ the equal sign is just how we start any function in Google Sheets.
• QUARTILE() is our QUARTILE function. It will return the closest value of the specified quartile of our dataset.
• data is the range that contains our dataset.
• quartile_number refers to the specific quartile we want to retrieve from our dataset. Note that this ranges from 0 to 5, with 0 being the minimum value in the dataset, 3 being the median value, and 5 as the maximum value.

A Real Example of Using QUARTILE Function

Take a look at the example below to see how QUARTILE functions are used in Google Sheets.

We have a dataset of 50 test scores in Column A. We also set up another table to see the values of every quartile in the dataset.

For getting the first quartile in cell D3, we just use the formula:

=QUARTILE(A2:$A$11,C3)

You may make a copy of the spreadsheet using the link I have attached below. 

Besides finding quartiles in Google Sheets, another common computation is finding the interquartile range. This is basically the range of the “middle fifty percent” of your dataset.

This can be easily computed with the following formula:

=QUARTILE(A2:A51,3) - QUARTILE(A2:A51,1)

Now that you’ve seen the function in action let’s begin writing our own QUARTILE function in Google Sheets.

How to Use QUARTILE Function in Google Sheets

1. Before we type our function, let’s populate our spreadsheet with a dataset. Let’s go with a dataset with 100 values. In this example, I used =RANDBETWEEN(0,100) to generate the dataset below. Let’s place our dataset in Column A.
   
   
   
2. To start using QUARTILE , we should select any cell to make it the current active cell. For this guide, cell B1 will be where we will input our function’s result.
3. Next, simply type the equal sign ‘=‘ to begin the function, followed by the name of the function we’ll be using, which is ‘QUARTILE‘. 
4. You should find that the auto-suggest box shows you the QUARTILE function even before fully typing it out. You may press the Tab button on your keyboard to let Google Sheets auto-fill your Formula Bar with “QUARTILE(“.
   As seen below, a tooltip box appears with info on the selected function. We simply hit the arrow on the top-right hand corner of the box to minimize it. 
   
5. Let’s type out the required arguments. The range needed is A2:A101, and for now, let’s output the first quartile which we indicate with a “1”.
   
6. We found the first quartile of our dataset! All you need to do to find the other quartiles is to modify the second argument of your QUARTILE function.
   

That’s basically all you need to know about the QUARTILE function. Hopefully, this step-by-step guide has made it easy for you to find quartiles in Google Sheets.

You can now use the QUARTILE functions in Google Sheets together with the various other Google Sheets formulas available to create more powerful worksheets for your convenience.

Do consider subscribing to our newsletter to be notified of more helpful Google Sheets guides like this one.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe