Percentile & Quartile

Excel Formulas › Statistics

All versionsPERCENTILEQUARTILE

Percentiles and quartiles tell you where a value sits in a distribution — the 90th-percentile response time, the top quartile of sales. PERCENTILE and QUARTILE compute them directly.

Quick formula: for the 90th percentile of a range:
=PERCENTILE(B2:B100, 0.9)
The second argument is the percentile as a decimal — 0.9 for the 90th. 90% of values fall at or below the result.

Functions used (tap for the full reference guide):

PERCENTILEQUARTILEMEDIAN

The example

A set of scores with key percentiles and quartiles.

ACD
1ScoreStatValue
260Q1 (25th)68
372Median (Q2)75
475Q3 (75th)84
58890th pct89.6
691

The formula

The 90th percentile and the first quartile:

=PERCENTILE(A2:A6, 0.9) → 89.6 =QUARTILE(A2:A6, 1) → Q1

How it works

Both rank the data and read off a position:

  1. PERCENTILE(range, 0.9) finds the value below which 90% of the data falls — interpolating between points as needed.
  2. QUARTILE(range, 1) is the 25th percentile (Q1); 2 is the median; 3 is the 75th percentile (Q3).
  3. So QUARTILE(range, 1) equals PERCENTILE(range, 0.25) — quartiles are just the quarter-point percentiles.
  4. Use these to define bands (“top 10%”), spot the spread, or build a box plot.

.INC vs .EXC. Modern Excel splits these into PERCENTILE.INC (inclusive, same as the classic PERCENTILE) and PERCENTILE.EXC (exclusive, excludes the 0th and 100th points). .INC matches the old behavior — use it unless you specifically need exclusive.

Try it: interactive demo

Live demo

Choose a percentile of the sample scores {60,72,75,88,91}.

Value:

Variations

Quartiles

0=min, 1=Q1, 2=median, 3=Q3, 4=max:

=QUARTILE(A2:A100, 3)

Modern .INC functions

The current names (identical to the classics):

=PERCENTILE.INC(A2:A100, 0.9)

A value's own percentile rank

Where does 80 sit?

=PERCENTRANK(A2:A100, 80)

Pitfalls & errors

Percentile as a percent, not a decimal. Use 0.9 for the 90th percentile, not 90 (which errors — it must be between 0 and 1).

.INC vs .EXC give different answers near the ends of the data. The classic PERCENTILE = .INC; only use .EXC if your method requires it.

Interpolation. Percentiles interpolate between data points, so the result often isn’t one of your actual values — that’s expected.

Practice workbook

📊
Download the free Percentile & Quartile practice workbook
Scores with live PERCENTILE and QUARTILE, the .INC and PERCENTRANK variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate a percentile in Excel?
Use =PERCENTILE(range, p) where p is a decimal, e.g. =PERCENTILE(B2:B100, 0.9) for the 90th percentile. 90% of values fall at or below the result.
How do I find quartiles in Excel?
Use QUARTILE with a quart number: 1 for Q1 (25th), 2 for the median, 3 for Q3 (75th). =QUARTILE(range, 3) returns the third quartile.
What's the difference between PERCENTILE.INC and PERCENTILE.EXC?
INC (inclusive) matches the classic PERCENTILE and includes the 0th and 100th points; EXC (exclusive) excludes them and gives different results near the extremes.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Median by group · Find outliers · Rank values (no gaps)

Function references: PERCENTILE · QUARTILE · MEDIAN