PERCENTILE and QUARTILE in Excel, Explained Simply

A percentile tells you where a value stands in a group: the 90th percentile is the value that 90% of the data is at or below. In Excel, PERCENTILE.INC finds any percentile and QUARTILE.INC finds the quarter points, Q1, the median and Q3. This guide is for anyone who has to read test scores, salaries, delivery times or survey results, and it explains both functions on one small list you can type in.

What are percentiles and quartiles?#

A percentile splits sorted data into 100 equal parts. The 25th percentile is the value a quarter of the data is at or below; the 90th percentile is the value 90% of the data is at or below.

Quartiles are just three special percentiles that split the data into four parts:

Percentiles are useful because, like the median, one extreme value doesn't drag them around the way it drags an average. The article on average vs median explains why that matters.

All the examples use these ten test scores in B2:B11:

Sorted from low to high, they are 52, 61, 64, 70, 73, 75, 78, 82, 88, 95. You don't have to sort the data yourself: the functions do it internally.

How to use PERCENTILE in Excel#

PERCENTILE.INC takes the data range and the percentile you want, written as a decimal from 0 to 1:

=PERCENTILE.INC(array, k)

To find the 90th percentile of the scores:

=PERCENTILE.INC(B2:B11,0.9)

The result is 88.7. You could also type 90% instead of 0.9, because Excel stores 90% as 0.9. Typing 90 gives #NUM!.

A few more values from the same list:

To flag the top 10% of candidates, put this in C2 and fill down (Windows: Ctrl+D; Mac: ⌘D):

=B2>=PERCENTILE.INC($B$2:$B$11,0.9)

Only Dev, with 95, shows TRUE. The $ signs keep the range fixed as the formula moves down.

How Excel calculates a percentile#

Excel finds a position in the sorted list, then reads the value there. For PERCENTILE.INC the position is:

k × (n − 1) + 1

Here n is how many values you have. For the 90th percentile of 10 scores, that is 0.9 × 9 + 1 = 9.1. The 9th value is 88 and the 10th is 95, so Excel goes 0.1 of the way from 88 towards 95: 88 + 0.1 × 7 = 88.7.

This step is called interpolation, and it is why a percentile is often a number that isn't in your list. Nobody scored 88.7, but it is still the correct 90th percentile.

How to use QUARTILE in Excel#

QUARTILE.INC is a shortcut for the quarter points. Its second argument is a whole number from 0 to 4:

=QUARTILE.INC(array, quart)

Q1 sits at position 0.25 × 9 + 1 = 3.25, a quarter of the way from 64 to 70, which is 65.5.

The gap between Q3 and Q1 is the interquartile range (IQR): 81 − 65.5 = 15.5. It tells you how spread out the middle half of the group is. A common rule marks a value as an outlier if it is more than 1.5 × IQR below Q1 or above Q3. Here that means below 42.25 or above 104.25, so this list has no outliers.

INC vs EXC: which version should you use?#

Excel has two methods, and they give slightly different answers. The .INC (inclusive) versions treat the lowest and highest values as the 0th and 100th percentiles. The .EXC (exclusive) versions don't, and they use the position k × (n + 1) instead.

Neither is wrong; they are different conventions used in different textbooks and tools. Use .INC by default. Switch to .EXC only when a course, a manager or another system asks for that method, so your numbers match theirs.

The older PERCENTILE and QUARTILE functions still work and give the same results as the .INC versions. The .INC and .EXC names arrived in Excel 2010 and work in every current version on Windows, Mac and the web.

Percentile vs rank, LARGE and SMALL#

Percentiles answer "what value marks this point?". Other functions answer neighbouring questions:

• PERCENTRANK.INC goes the other way: given a score, what percentile is it? =PERCENTRANK.INC(B2:B11,82) returns 0.777, so 82 is at about the 78th percentile. Excel cuts the result to three decimal places by default.
• LARGE and SMALL return the value at an exact position, such as the 2nd highest score, with no interpolation. =LARGE(B2:B11,2) returns 88. See how to find the top N values with LARGE and SMALL.
• RANK.EQ returns a value's place in the list, such as 3rd. The guide to the RANK function covers ties.
• MEDIAN is the 50th percentile, with a simpler formula. See MEDIAN and MODE in Excel.

To count how many values fall into each band, rather than find cut-off points, build a frequency table.

Why does PERCENTILE return #NUM!?#

PERCENTILE and QUARTILE return #NUM! when the second argument is out of range or there is no data to work with. Check these:

1. k is not between 0 and 1. Write 0.9 or 90%, not 90.
2. quart is not 0 to 4. QUARTILE.EXC accepts only 1, 2 and 3.
3. k is too close to 0 or 1 for .EXC. With 10 values, PERCENTILE.EXC needs k between 1/11 and 10/11, so =PERCENTILE.EXC(B2:B11,0.05) fails. Use .INC.
4. The range has no numbers, for example because the scores were imported as text. Convert them to numbers first.

Blank cells and text inside the range are ignored, just like in MEDIAN.

Practise it#

PERCENTILE builds on two skills you can practise right now: finding the middle of a list and picking values by position. Practise MEDIAN and MODE in a live spreadsheet, where you type each formula and it is checked instantly. Then try the LARGE and SMALL lesson to pull the top and bottom values. Start with the exercise on this page: the median is the 50th percentile.