Spreadsheet Practice

How to calculate quartiles in Excel

Formula (Excel and Google Sheets)

=QUARTILE.INC(B2:B11,1)

Use =QUARTILE.INC(range, 1) for Q1, =QUARTILE.INC(range, 2) for the median and =QUARTILE.INC(range, 3) for Q3. The last argument picks the quartile, 0 to 4.

Result on the example
34.25 in G2
Works in
Excel 2010 and later
Google Sheets
Same formula

See it work

The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.

How it works

Quartiles cut sorted data into four equal parts. Q1 is the value a quarter of the way up the sorted list, Q3 is three quarters of the way, and quartile 2 is the median.

QUARTILE.INC interpolates between values when the position falls between two of them. QUARTILE.EXC uses a different method that excludes the endpoints, so the two can disagree on small data sets.

=QUARTILE.INC(B2:B11,1)

Result34.25

  1. B2:B1110 rows × 1 col: 34, 38, 41, 29, …

    A range: 10 cells in one column.

  2. QUARTILE.INC(B2:B11,1)34.25

    QUARTILE.INC: Quartile 0–4: minimum, first quartile, median, third quartile, maximum.

    • arrayB2:B1110 rows × 1 col: 34, 38, 41, 29, …
    • quart1

In Google Sheets

The same formula works in Google Sheets: =QUARTILE.INC(B2:B11,1). We ran it in Google Sheets mode on the same data and it returns 34.25.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=PERCENTILE.INC(B2:B11,0.25)34.25The same answer written as a percentile: 25% is Q1.
=QUARTILE(B2:B11,1)34.25The older name, kept for compatibility. It matches QUARTILE.INC.

Common mistakes

  • The quartile argument runs from 0 (minimum) to 4 (maximum). Passing 5 returns #NUM!.
  • Blank cells and text in the range are ignored, but zeros are counted.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate quartiles

In G2, find the first quartile (Q1) of the delivery minutes in B2:B11.

Questions

What is the difference between QUARTILE.INC and QUARTILE.EXC?

INC includes the minimum and maximum when interpolating; EXC excludes them. For most reports use INC, which matches the older QUARTILE function.

How do I get the interquartile range?

Subtract: =QUARTILE.INC(B2:B11,3)-QUARTILE.INC(B2:B11,1).

Checked by running on 2026-11-20: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.