Excel PERCENTILE and QUARTILE Functions

last modified April 4, 2025

The PERCENTILE and QUARTILE functions are powerful statistical tools in Excel. They help analyze data distribution by finding values at specific percentiles or quartiles. This tutorial provides a comprehensive guide to using these functions. You’ll learn their syntax, differences, and practical applications with detailed examples.

PERCENTILE/QUARTILE Basics

PERCENTILE returns the value at a specific percentile in a dataset. QUARTILE is a special case that returns values at the 0%, 25%, 50%, 75%, and 100% points. Both help understand data distribution.

Function
Description


PERCENTILE
Returns value at given percentile (0-1)


PERCENTILE.INC
Inclusive version (Excel 2010+)


PERCENTILE.EXC
Exclusive version (Excel 2010+)


QUARTILE
Returns value at quartile (0-4)


QUARTILE.INC
Inclusive version (Excel 2010+)


QUARTILE.EXC
Exclusive version (Excel 2010+)

This table shows the different variations of these functions. The .INC versions include 0 and 1 percentiles, while .EXC versions exclude them. QUARTILE is essentially PERCENTILE for specific points (0%, 25%, 50%, 75%, 100%).

Basic PERCENTILE Example

This example demonstrates finding the 90th percentile in a dataset of test scores. The percentile shows the score below which 90% of scores fall.

A
B


78



85



92



88



95




=PERCENTILE.INC(A1:A5, 0.9)

Basic PERCENTILE formula

=PERCENTILE.INC(A1:A5, 0.9)

This formula calculates the 90th percentile of test scores in A1:A5. The result will be approximately 93.8, meaning 90% of scores are below this value. The .INC version includes the 0th and 100th percentiles in calculations.

QUARTILE Example with Sales Data

This example uses QUARTILE to analyze quarterly sales data distribution. It shows how to find all five quartile points (minimum, Q1, median, Q3, maximum).

A
B


12500



18700



14300



21000



16500




=QUARTILE.INC(A1:A5, 0)



=QUARTILE.INC(A1:A5, 1)



=QUARTILE.INC(A1:A5, 2)



=QUARTILE.INC(A1:A5, 3)



=QUARTILE.INC(A1:A5, 4)

QUARTILE formulas

=QUARTILE.INC(A1:A5, 0) // Minimum =QUARTILE.INC(A1:A5, 1) // First quartile (25%) =QUARTILE.INC(A1:A5, 2) // Median (50%) =QUARTILE.INC(A1:A5, 3) // Third quartile (75%) =QUARTILE.INC(A1:A5, 4) // Maximum

These formulas calculate all five quartile points for the sales data. Quartile 0 is the minimum (12500), Q1 is ~14400, median is 16500, Q3 is ~19600, and Q4 is the maximum (21000). This gives a complete picture of data distribution.

PERCENTILE.EXC vs PERCENTILE.INC

This example demonstrates the difference between the inclusive and exclusive versions of PERCENTILE. The .EXC version excludes 0% and 100% percentiles.

A
B
C


15
=PERCENTILE.INC(A1:A5, 0)
=PERCENTILE.EXC(A1:A5, 0)


22
=PERCENTILE.INC(A1:A5, 0.5)
=PERCENTILE.EXC(A1:A5, 0.5)


30
=PERCENTILE.INC(A1:A5, 1)
=PERCENTILE.EXC(A1:A5, 1)


18




25

PERCENTILE comparison

=PERCENTILE.INC(A1:A5, 0) // Returns 15 (minimum) =PERCENTILE.EXC(A1:A5, 0) // Returns #NUM! error =PERCENTILE.INC(A1:A5, 1) // Returns 30 (maximum) =PERCENTILE.EXC(A1:A5, 1) // Returns #NUM! error

The .INC version works with 0 and 1 percentiles, returning min/max values. The .EXC version returns errors for these cases as it excludes extremes. For 0.5 (median), both return similar values (~23.5 in this case).

Using QUARTILE for Outlier Detection

This example shows how to use QUARTILE to identify potential outliers using the interquartile range (IQR) method. IQR is Q3-Q1, and outliers are typically values below Q1-1.5IQR or above Q3+1.5IQR.

A
B


12
=QUARTILE.INC(A1:A10,1)


15
=QUARTILE.INC(A1:A10,3)


18
=B1-1.5*(B2-B1)


20
=B2+1.5*(B2-B1)


22



25



28



32



35



120

Outlier detection formulas

=QUARTILE.INC(A1:A10,1) // Q1 (18) =QUARTILE.INC(A1:A10,3) // Q3 (32) =B1-1.5*(B2-B1) // Lower bound (-3) =B2+1.5*(B2-B1) // Upper bound (53)

This calculates Q1 (18) and Q3 (32), then determines outlier thresholds. The value 120 exceeds the upper bound (53), identifying it as a potential outlier. This method is commonly used in statistical analysis.

PERCENTILE with Conditional Data

This advanced example combines PERCENTILE with FILTER to calculate percentiles for specific subsets of data. Here we find the 75th percentile for sales in the East region only.

A (Region)
B (Sales)
C


East
12000



West
15000



East
18000



North
9000



East
21000





=PERCENTILE.INC(FILTER(B1:B5,A1:A5="East"),0.75)

Conditional PERCENTILE

=PERCENTILE.INC(FILTER(B1:B5,A1:A5=“East”),0.75)

This formula first filters to only East region sales (12000, 18000, 21000), then calculates the 75th percentile (~19500). This technique is powerful for analyzing specific segments within larger datasets.

The PERCENTILE and QUARTILE functions are essential for statistical analysis in Excel. They help understand data distribution, identify outliers, and compare subsets. Remember that .INC includes min/max while .EXC excludes them. Mastering these functions provides valuable insights into your data’s characteristics.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.