Understanding Percentile Rank in Google Sheets

August 7th, 2026

Percentiles place a value on a 0–100 scale relative to a dataset: the 90th percentile is the cutoff above which only about 10% of values fall. Google Sheets exposes this idea through PERCENTILE, PERCENTILE.INC, PERCENTILE.EXC, PERCENTRANK, and QUARTILE. This guide shows how to compute and interpret them, how inclusive vs exclusive variants differ, and optional outlier fences with Q1/Q3.

Percentile vs percent rank (two directions)

• Percentile functions — start from a percentage p, return the value at that position in the distribution.
• Percent rank — start from a value, return its relative standing as a fraction (0–1) or percentage.

=PERCENTILE(A2:A100, 0.9)
=PERCENTRANK(A2:A100, B2)

If the 90th percentile of scores is 87, then roughly 90% of scores are at or below 87 (inclusive definitions). If PERCENTRANK for a student score is 0.82, that student sits around the 82nd percentile of the class.

PERCENTILE and PERCENTILE.INC

PERCENTILE returns the value at a given percentile. The second argument is a fraction between 0 and 1 (not 0–100).

=PERCENTILE(B2:B50, 0)
=PERCENTILE(B2:B50, 0.25)
=PERCENTILE(B2:B50, 0.5)
=PERCENTILE(B2:B50, 0.75)
=PERCENTILE(B2:B50, 1)

Those are min, first quartile, median, third quartile, and max under the inclusive definition. PERCENTILE.INC is the same inclusive algorithm under a more explicit name; in Google Sheets you can treat PERCENTILE and PERCENTILE.INC as the inclusive family.

=PERCENTILE.INC(B2:B50, 0.9)

Practical cutoffs

=PERCENTILE(Sales, 0.95)

Use that as a “top 5% threshold” for bonuses, alerts, or segment labels. Compare each row:

=C2>=PERCENTILE($C$2:$C$200, 0.95)

PERCENTILE.EXC — exclusive percentiles

PERCENTILE.EXC uses an exclusive definition: the percentile argument must be strictly between 0 and 1, and the scaling of ranks differs slightly so that 0% and 100% are not returned as the sample min/max in the same way.

=PERCENTILE.EXC(B2:B50, 0.25)
=PERCENTILE.EXC(B2:B50, 0.5)
=PERCENTILE.EXC(B2:B50, 0.75)

For small samples, INC and EXC can disagree. For large samples they usually sit close. Prefer INC unless a standard or academic method explicitly requires exclusive percentiles. Invalid args for EXC (including 0 and 1) produce errors — do not pass min/max percentiles to EXC.

PERCENTRANK — where does this value sit?

PERCENTRANK returns the percentage rank of a value within a range, as a decimal between 0 and 1.

=PERCENTRANK(A2:A100, A2)
=PERCENTRANK(A2:A100, A2, 2)

The optional third argument sets the number of significant digits in the result (display precision of the rank). Format the cell as a percentage if you want 82% instead of 0.82.

Rank every row

In a helper column:

=PERCENTRANK($C$2:$C$100, C2)

Fill down. Sort or filter by that column to find top/bottom performers relative to peers — without inventing absolute cutoffs in advance.

Related: PERCENTRANK.INC / PERCENTRANK.EXC

Sheets also supports inclusive/exclusive percent-rank variants analogous to percentile INC/EXC. For everyday dashboards, PERCENTRANK (inclusive style) is enough; switch only when a methodology document demands EXC.

QUARTILE — percentiles at 0%, 25%, 50%, 75%, 100%

QUARTILE is convenience sugar for common percentiles:

=QUARTILE(B2:B50, 0)
=QUARTILE(B2:B50, 1)
=QUARTILE(B2:B50, 2)
=QUARTILE(B2:B50, 3)
=QUARTILE(B2:B50, 4)

| Quartile arg | Meaning | Same idea as | | --- | --- | --- | | 0 | Minimum | PERCENTILE(..., 0) | | 1 | Q1 (25th) | PERCENTILE(..., 0.25) | | 2 | Median (50th) | PERCENTILE(..., 0.5) | | 3 | Q3 (75th) | PERCENTILE(..., 0.75) | | 4 | Maximum | PERCENTILE(..., 1) |

Quartiles are ideal for box-plot style summaries and IQR-based rules.

Interpreting percentiles without fooling yourself

• Percentile is relative, not absolute. Being in the 90th percentile of a weak cohort is not the same as 90th percentile of a strong one.
• Ties and discrete data. Many people can share a rank when values repeat (test scores, star ratings).
• Interpolation. For percentiles between observed points, Sheets interpolates — the returned value may not equal any raw data point.
• Sample size. The 99th percentile of 20 points is unstable; collect more data before making policy on extreme tails.
• “Top 10%” vs “90th percentile value.” Filtering rows with PERCENTRANK >= 0.9 and thresholding with >= PERCENTILE(..., 0.9) are related but can differ slightly with ties and inclusive rules — pick one definition and stick to it.

Optional: outlier fences with Q1 and Q3

A common robust rule flags points outside the “fences” built from the interquartile range (IQR):

=QUARTILE(B2:B100, 1)
=QUARTILE(B2:B100, 3)

=Q3 - Q1

With Q1 in E1, Q3 in E2:

=E2-E1
=E1-1.5*(E2-E1)
=E2+1.5*(E2-E1)

Those last two cells are lower and upper fences (Tukey-style with multiplier 1.5). Flag a value in B2:

=OR(B2<$E$4, B2>$E$5)

This is optional analytics, not a law of nature — adjust the multiplier or use domain rules when 1.5×IQR is too aggressive or too loose. Percentile tails (PERCENTILE(..., 0.01) / 0.99) are another simple way to mark extremes without the IQR story.

Practical examples

SLA: 95th percentile latency

=PERCENTILE(LatencyMs, 0.95)

Ops teams often manage to p95 or p99 rather than the mean, because averages hide rare slow calls.

Grade curve labels

=IF(C2>=PERCENTILE($C$2:$C$40, 0.9), "A",
  IF(C2>=PERCENTILE($C$2:$C$40, 0.7), "B",
  IF(C2>=PERCENTILE($C$2:$C$40, 0.4), "C", "D")))

Relative grading in one nested IF — replace cutoffs as needed.

IQR summary block

=QUARTILE(B2:B100, 1)
=QUARTILE(B2:B100, 2)
=QUARTILE(B2:B100, 3)

Three cells give you a compact distribution snapshot next to AVERAGE and STDEV.

Common mistakes

• Passing 90 instead of 0.9. Percentile arguments are fractions 0–1. PERCENTILE(range, 90) is invalid.
• Mixing INC and EXC in one workbook. Pick one convention for published metrics.
• Percent rank of a value outside the range. Behavior may error or extrapolate depending on function variant — keep comparisons inside the observed set unless you know you need otherwise.
• Using percentiles on unsorted data. You do not need to sort first; the functions handle order internally.
• Confusing PERCENTRANK with RANK. RANK returns 1, 2, 3… standings; PERCENTRANK returns a 0–1 relative position.

Which function should you pick?

| Goal | Function | | --- | --- | | Value at the 90th percentile | PERCENTILE / PERCENTILE.INC | | Exclusive percentile definition | PERCENTILE.EXC | | Standing of one value (0–1) | PERCENTRANK | | Q1 / median / Q3 quickly | QUARTILE | | Outlier fences | Q1, Q3, IQR ± 1.5×IQR |

Going further

Percentiles reframe raw numbers as position in a distribution — essential for SLAs, grading curves, compensation bands, and robust outlier checks. Start with PERCENTILE and QUARTILE for cutoffs, add PERCENTRANK when each row needs a standing, and document whether you use inclusive or exclusive definitions. For more statistical tools in Sheets, see Understanding Statistical Functions in Google Sheets and the function pages linked above.