If you want to find the value that splits your dataset at the 25, 50, or 75 percent mark, the QUARTILE function in Google Sheets does it in one call. You hand it a range and a quart number, and it returns the cutoff for that quartile.
I’ll walk through five examples, covering the first quartile, the median, the third quartile, the min/max trick, and how to build an interquartile range.
QUARTILE Function Syntax in Google Sheets
QUARTILE returns the value at a chosen quartile of a numeric dataset.
=QUARTILE(data, quartile_number)
- data is the range of numbers you want to analyze.
- quartile_number picks which cutoff you want. Use 0 for the minimum, 1 for the first quartile (25%), 2 for the median (50%), 3 for the third quartile (75%), and 4 for the maximum.
When to Use QUARTILE Function
- Spot the bottom 25 percent cutoff in test scores, response times, or any list of numbers.
- Pull the median out of a column without a separate MEDIAN call.
- Find the top 25 percent threshold so you can flag high values for review.
- Build the interquartile range by subtracting Q1 from Q3, a common spread measure.
- Pair with conditional formatting to highlight rows that fall above or below a quartile cutoff.
Example 1: First Quartile of Test Scores
Let’s start with the most common use, finding the 25 percent cutoff.
Below is the dataset. Column A has five test scores ranging from 55 to 95.
I want the value below which the bottom 25 percent of scores sit.
Here is the formula:
=QUARTILE(A2:A6, 1)
QUARTILE sorts the values internally and finds the 25 percent cutoff. With five values, that lands on the second smallest score, which is 60. So one out of every four scores sits at or below 60.
Q1 is useful when you want to ignore the bottom of the distribution. Scores at or below it are the lowest quarter, the rest are everyone else.
Example 2: Median Sale Price With QUARTILE
The second quartile is the median, the middle value of the sorted dataset.
Below is the dataset. Column A has nine sale prices in 20-unit steps from 120 to 280.
I want the value that splits the list into a lower and upper half.
Here is the formula:
=QUARTILE(A2:A10, 2)
With nine sorted values, the middle one is the fifth value, 200. Four prices sit below it and four sit above. That is the median.
You could also call MEDIAN directly and get the same answer. QUARTILE just lets you pull any of the five common cutoffs from one function, which is handy when you want a few of them side by side.
Example 3: Third Quartile of Order Values
The third quartile marks the cutoff for the top 25 percent of values.
Below is the dataset. Column A has nine order values, the same step pattern as the previous example.
I want the value below which 75 percent of the orders fall.
Here is the formula:
=QUARTILE(A2:A10, 3)
QUARTILE picks the 75 percent point in the sorted list. For these nine values, that lands at 240. Three of every four orders sit at or below 240, and the top quarter is anything above it.
That cutoff is useful for spotting top performers, expensive purchases, or any “above this is the top 25 percent” rule you need.
Pro Tip: QUARTILE uses the inclusive method, which matches QUARTILE.INC exactly. For small datasets where you want strictly between the endpoints, use QUARTILE.EXC instead. The two can differ by a few units when the sample size is tiny.
Example 4: Minimum and Maximum With QUARTILE
You can also use QUARTILE to grab the smallest and largest values.
Below is the dataset. Column A has five response times in seconds.
I want the smallest value, which is what quartile 0 gives back.
Here is the formula:
=QUARTILE(A2:A6, 0)
Passing 0 as the second argument tells QUARTILE to return the lowest value in the range, which is 12. Passing 4 would return the largest, which is 40 here. Those two values match what MIN and MAX return.
This is mostly useful when you already have a column of QUARTILE formulas. Adding 0 and 4 to that column lets you build the full five-number summary, which is min, Q1, median, Q3, and max, all using the same function.
Example 5: Interquartile Range From QUARTILE
The interquartile range, IQR, is the gap between the third and first quartiles. It is a common way to measure spread.
Below is the dataset. Column A has five daily sales figures evenly spaced from 200 to 1000.
I want the IQR, which is Q3 minus Q1.
Here is the formula:
=QUARTILE(A2:A6, 3) - QUARTILE(A2:A6, 1)
QUARTILE 3 gives you 800 and QUARTILE 1 gives you 400, so the difference is 400. That number tells you the middle half of the data spans a 400-unit range.
IQR is less sensitive to outliers than the full range (max minus min), which is why it shows up in box plots and outlier checks. One huge sale on a freak day will not blow up the IQR the way it will the average.
Tips & Common Mistakes
- Quart numbers run from 0 to 4. Pass anything outside that range and you get a
#NUM!error. Pass a decimal like 1.5 and Sheets truncates it to 1 before computing. - Empty cells and text are ignored. QUARTILE only looks at numbers in the range, so a stray header label or blank row won’t break the formula. But TRUE and FALSE values are skipped too, so be mindful if your column has those.
- Use QUARTILE.EXC for the exclusive method. Plain QUARTILE matches QUARTILE.INC. If your stats workflow needs the exclusive percentile method, switch to QUARTILE.EXC. The two diverge most on small samples.
QUARTILE is a quick way to pull any of the five standard cutoffs from a column of numbers.
You’ve now seen it find the first quartile, the median, the third quartile, the minimum, and the interquartile range. Plug in the quart number you need and QUARTILE handles the sort and the math for you.
List of All Google Sheets Functions
Related Google Sheets Functions / Articles: