How to Find Quartiles in Google Sheets (Easy Guide)

The quartile function in Google Sheets makes it simple to calculate quartiles without having to manually reorder your data from smallest to largest.

Quartiles are markers at three points in a dataset. They include:

• The median (middle number)
• The halfway point between the minimum number and median
• The halfway point between the median and the maximum number

The quartile function in Google Sheets also uses the min and max numbers in the dataset.

Therefore, the quartile function uses five quartile numbers to identify each marker including the minimum, maximum, median, and two quartile points.

The numbers used are 0,1, 2, 3, and 4 where 0 returns the minimum value and 4 is the maximum.

The Benefits of Using Quartiles in Google Sheets

You can use quartile calculations to figure out deviations in your data. Whether you’re a small business or analyzing data for school work there are plenty of reasons to use quartiles in Google Sheets, such as:

• Scoring tests and assigning grades based on the quartile markers
• Checking pay scale limits for jobs
• Analyzing sales figures

Calculating Quartiles Manually (Finding the First and Third Quartile)

To find the quartiles you need to know the minimum, maximum, and median (middle) points of the data set. To figure out the quartile on each side of the median, you simply have to figure out the middle points between the median and max figure, as well as the median and min value.

Quartile Function Syntax For Google Sheets

The function will send back the closest value to a specified quartile in your dataset.

The syntax used for this formula is:

=Quartile(data, quartile_number)

• Data – The range or array of data to be considered for the calculation

• Quartile_number – Which quartile number between 0 and 4 to send back
  • 0 – Gives the minimum value which is the same as using the MIN function
  • 1 – Gives the value for the first quartile in the dataset
  • 2 – Gives the value of the median
  • 3 – Gives the value of the second quartile
  • 4 – Gives the maximum value which is the same as using the MAX function

An Example of Quartiles in Google Sheets

In the above example, you can see we’ve used the syntax =Quartile(data, quartile_number) in which the data value has been substituted with the data set of A2:A8, and each quartile number from 0-4 has been represented too.

You just have to put the data set as the first argument and the quartile number as the second. It’s a fairly simple function so long as you remember which number from 0 to 4 represents each quartile marker.

The Complete Breakdown of How to Find Quartiles in Google Sheets

In the above quartile Google Sheets example, to get the results in the boxes you would have to follow these steps:

1. Click the cell you want the result to show in. B2 in the example.
2. Type =qu then click on the quartile function
3. Click and drag over the desired dataset or manually type in the array.
   
4. Type a comma , on your keyboard
5. Enter the desired quartile number from 0 to 4 and press the end bracket key on your keyboard
6. Press enter

Another related function to Quartile is the Percentile function. Read more about how to use the percentile function.

How to Find the IQR in Google Sheets

There’s no direct interquartile range Google Sheets function. You simply have to find the 1st and 3rd quartile to identify the IQR.

How to Calculate Quartiles in Google Sheets FAQs

Is the Quartile Formula Google Sheets Based Function Compatible with Microsoft Excel?

Microsoft has replaced the quartile query with quartile.inc and quartile.exc in Excel. The original quartile function still works, but it may be best to use one of the other functions just in case Microsoft makes it incompatible in the future.

If you plan to use your spreadsheet across platforms you will need to keep this in mind. These newer functions are both available in Google Sheets, so you don’t need to stress.

Quartile.inc uses the exact same syntax as the quartile function we already discussed. It uses the values of 0 to 4 to represent each quartile inclusive of the minimum and maximum values.

The syntax for quartile.exc is similar but only uses the values of 1 to 3 as it doesn’t recognize the minimum and maximum numbers of the dataset as part of its function.

That means you will get a completely different result if you use the .exc version.

To make it simple, just remember the quartile.inc function includes the min and max of the dataset and the quartile.exc function excludes them.

What Do Percentiles Mean When Talking About Quartiles?

As quartiles divide data up into 4 sections they are sometimes referred to as percentiles in groups of 25% (25% x 4 = 100%).

In this case:

• The first quartile is called the 25th percentile as 25% of the data lies beneath this number (represented by 1 in the Google Sheets formula)
• The second quartile or median is the 50th percentile as 50% of the data is below this point (2 in the syntax formula)
• The third quartile is the 75th percentile as 75% of the data lies beneath this point (3 in the Sheets syntax)

How Do You Find the Quartiles in Google Sheets? How Are Quartiles Calculated? How Do You Do Quartiles in Google Sheets?

1. Click and empty cell
2. Type =QUARTILE(
3. Click and drag of the range of data
4. Type a comma
5. Type 0, 1, 2, 3, or 4 to find which quartile you want
6. Press Enter

How Do You Find Q1 and Q3 on Sheets?

You can’t do it in one cell. You need to do one quartile calculation for Q1 and Q3 but once you know how to find quartile 1 and quartile 3 in Google Sheets, you can do them all. 

1. Type =QUARTILE( into an empty cell
2. Drag over the range you wish to inspect
3. Type a comma, then type 1 to find the first quartile
4. Repeat using 3 instead of 1 to find the third quartile

What Is the Quartile Function in Google Sheets?

The quartile function helps you find useful data points to complete more accurate calculations based on large data sets. You could basically think of it as finding the middle point, then the middle point between that and the lowest score, also the highest.

Is There a QUARTILEIF Google Sheets Function?

No, but you could use a nested function to assign IF parameters. Like so:

=QUARTILE(IF((B2:B20>2010)*(B2:B20<2015),B2:B10),3)

Conclusion

After reading this article, you should now know how to find quartiles in Google Sheets. Let us know in the comments if you have any questions and check out our other articles to become a spreadsheet master!

Other Google Sheets tutorials you may like:

• How to Use Pi in Google Sheets
• How to Calculate Percentage in Google Sheets