How to Find Quartiles in Excel: A Complete Guide

Quartiles are one of the most practical tools in statistical analysis — they tell you how data is distributed across four equal segments, making it easy to spot medians, outliers, and ranges at a glance. Whether you're analyzing sales figures, student scores, or survey responses, Excel makes quartile calculation straightforward once you understand the functions available.

What Is a Quartile?

A quartile divides a ranked dataset into four equal parts:

Q1 (First Quartile) — the 25th percentile; 25% of values fall below this point
Q2 (Second Quartile) — the 50th percentile; this is the median
Q3 (Third Quartile) — the 75th percentile; 75% of values fall below this point
Q4 — typically refers to the maximum value of the dataset

The range between Q1 and Q3 is called the Interquartile Range (IQR), which is widely used to identify outliers and understand data spread.

The Two Excel Quartile Functions

Excel offers two versions of the quartile function, and the difference between them matters depending on your dataset and methodology.

Function	Syntax	Calculation Method
`QUARTILE.INC`	`=QUARTILE.INC(array, quart)`	Includes the minimum and maximum values
`QUARTILE.EXC`	`=QUARTILE.EXC(array, quart)`	Excludes the minimum and maximum values
`QUARTILE` (legacy)	`=QUARTILE(array, quart)`	Identical to `QUARTILE.INC`; kept for backward compatibility

The quart argument is a number from 0 to 4:

0 = minimum value
1 = Q1 (25th percentile)
2 = Q2 (50th / median)
3 = Q3 (75th percentile)
4 = maximum value

📊 Note: QUARTILE.EXC only accepts quart values of 1, 2, or 3 — it will return an error if you pass 0 or 4.

Step-by-Step: Calculating Quartiles in Excel

Step 1 — Enter or select your data

Place your dataset in a column or row. For this example, assume your data is in cells A2:A20.

Step 2 — Choose an empty cell for your result

Click on any blank cell where you want the quartile value to appear.

Step 3 — Enter the formula

To find Q1:

=QUARTILE.INC(A2:A20, 1)

To find Q2 (median):

=QUARTILE.INC(A2:A20, 2)

To find Q3:

=QUARTILE.INC(A2:A20, 3)

Step 4 — Calculate the IQR (optional but useful)

In a separate cell, subtract Q1 from Q3:

=QUARTILE.INC(A2:A20, 3) - QUARTILE.INC(A2:A20, 1)

This gives you the Interquartile Range in a single formula without storing intermediate values.

QUARTILE.INC vs. QUARTILE.EXC — Which One Should You Use?

The choice between these two functions isn't cosmetic — they can return meaningfully different results, especially on smaller datasets.

QUARTILE.INC uses an interpolation method that treats the dataset's minimum and maximum as the 0th and 100th percentiles. This is the standard approach used in most business contexts and matches what many statistics textbooks describe.

QUARTILE.EXC treats the percentile boundaries as exclusive, which some statisticians prefer because it avoids assigning 0% or 100% probability to observed data points. This method aligns more closely with certain academic and analytical conventions.

For everyday business analysis — sales reports, performance reviews, financial summaries — QUARTILE.INC is the more commonly expected output. For statistical research or work that needs to match output from R, Python, or specific academic methods, QUARTILE.EXC may be the appropriate choice.

Working with Named Ranges and Tables 🔍

If your data is in an Excel Table (created via Insert → Table), you can reference the column by name instead of a cell range:

=QUARTILE.INC(Table1[Sales], 1)

This keeps your formula readable and automatically adjusts when rows are added or removed — a significant advantage in live datasets.

Named ranges work the same way. If you've named your data range ScoreData, the formula becomes:

=QUARTILE.INC(ScoreData, 1)

Common Errors and What They Mean

Error	Likely Cause
`#NUM!`	Using quart value 0 or 4 with `QUARTILE.EXC`
`#VALUE!`	Non-numeric values in the array range
`#REF!`	The referenced cell range has been deleted or moved

If your data column contains text labels mixed with numbers, Excel will skip the text values in most cases — but it's good practice to isolate clean numeric data before running any statistical function.

Variables That Affect Which Approach Works Best

Several factors determine which quartile method and formula structure will serve your situation:

Dataset size — smaller datasets show more divergence between INC and EXC results; larger sets converge
Excel version — QUARTILE.INC and QUARTILE.EXC were introduced in Excel 2010; older versions only have the legacy QUARTILE function
Compatibility needs — if your file will be opened in Google Sheets or LibreOffice Calc, function support may vary slightly
Who interprets the output — business stakeholders may expect INC-style quartiles; data science collaborators may expect EXC-style
Whether you're building a box plot — Excel's built-in box and whisker chart uses its own quartile calculation, which may not match your manual formulas exactly

Understanding quartiles in Excel is genuinely simple once you've run the formulas a few times. The more nuanced part is knowing which function fits your analytical context — and that depends on your data, your audience, and how your output will be used downstream.