Finding Quartiles in Excel: The Four Functions That Divide Your Data

Excel has four ways to find quartiles, and which one you use depends on your data and what you're measuring

A quartile is a value that divides your data into four equal parts. The first quartile (Q1) marks where 25 percent of your data falls below it. The second quartile (Q2) is the median — 50 percent below. The third quartile (Q3) is where 75 percent falls below. Excel gives you QUARTILE, QUARTILE.INC, QUARTILE.EXC, and PERCENTILE functions to find these values, and they calculate slightly differently depending on how they handle the edges of your dataset.

The difference matters most when your dataset is small or when you're comparing results across different tools. A spreadsheet with 100 sales figures will give you nearly identical results no matter which function you pick. A spreadsheet with 12 monthly values might show a noticeable gap. Understanding which function fits your situation means your quartile analysis will match what your audience expects to see.

Key Takeaways

QUARTILE.INC is the standard choice for most business data because it treats your smallest and largest values as part of the quartile calculation.
QUARTILE.EXC excludes the extremes and works better when you want to ignore outliers or when your data represents a sample rather than a complete population.
The PERCENTILE function does the same job as QUARTILE but lets you specify any percentile (not just 25, 50, and 75), making it more flexible for custom analysis.
All three functions use the same basic syntax: type the function name, then your data range in parentheses, then a number from 0 to 4 (or 0 to 100 for PERCENTILE) to specify which quartile you want.

Using QUARTILE.INC for standard business analysis

QUARTILE.INC is the function most people reach for first, and it's the right choice when you're analyzing complete datasets like monthly sales, employee salaries, or test scores for an entire class. The ".INC" stands for "inclusive" — it includes your minimum and maximum values in the calculation, treating them as part of the quartile boundaries.

To use it, type =QUARTILE.INC(data range, quartile number) into an empty cell. Replace "data range" with the cells holding your numbers — for example, A2:A101 if your sales data runs from row 2 to row 101. Replace "quartile number" with 0 (minimum), 1 (first quartile), 2 (median), 3 (third quartile), or 4 (maximum). If you have sales figures in cells A2 through A51 and you want the third quartile, you would type =QUARTILE.INC(A2:A51,3) and press Enter.

QUARTILE.INC works well when your data represents the whole group you're studying. If you're looking at every sale your company made last month, or every employee in your department, QUARTILE.INC will give you the boundaries that actually divide your real data into quarters.

Using QUARTILE.EXC when you want to exclude extremes

QUARTILE.EXC (exclusive) calculates quartiles by ignoring your smallest and largest values. Use this when you have outliers that skew your picture — a dataset with one unusually high sale, or one unusually low test score — and you want the quartiles to reflect the middle bulk of your data instead.

The syntax is identical to QUARTILE.INC: =QUARTILE.EXC(data range, quartile number). If you have 50 employee salaries in B2:B51 and you want to find where the middle 50 percent of salaries fall (between Q1 and Q3), you would calculate both =QUARTILE.EXC(B2:B51,1) and =QUARTILE.EXC(B2:B51,3) in separate cells, then subtract the first from the second.

QUARTILE.EXC is also the standard choice when your data is a sample drawn from a larger population. If you surveyed 30 customers out of thousands, QUARTILE.EXC treats your sample as representative of the whole group rather than as a complete dataset in itself.

Using PERCENTILE when you need flexibility beyond quartiles

The PERCENTILE function does everything QUARTILE does, but it lets you ask for any percentile, not just the four quartile points. If you want to know the 90th percentile (where 90 percent of your data falls below), or the 10th percentile, or the 33rd percentile, PERCENTILE gets you there in one step.

The syntax is =PERCENTILE(data range, percentile as decimal). For percentiles, you express the number as a decimal between 0 and 1, not as a whole number. To find the 75th percentile (which is the same as the third quartile), you would type =PERCENTILE(A2:A101, 0.75). To find the 90th percentile, type =PERCENTILE(A2:A101, 0.90). The 25th percentile (first quartile) is =PERCENTILE(A2:A101, 0.25).

PERCENTILE uses the inclusive method by default, treating it the same way as QUARTILE.INC. If you need the exclusive version, use PERCENTILE.EXC instead, with the same decimal syntax.

Step-by-step example: Finding quartiles for a dataset

Suppose you have 20 test scores in cells C2:C21 and you want to divide them into quartiles to see how your class performed. Click on an empty cell — say E2 — and type =QUARTILE.INC(C2:C21,1), then press Enter. This gives you the first quartile, the score below which 25 percent of students fell. In cell E3, type =QUARTILE.INC(C2:C21,2) for the median. In E4, type =QUARTILE.INC(C2:C21,3) for the third quartile.

Now you can see the boundaries: students scoring below the E2 value are in the bottom quarter, students between E2 and E3 are in the second quarter, students between E3 and E4 are in the third quarter, and students above E4 are in the top quarter. You can label these cells in column D (Q1, Q2, Q3) so the results are clear when you come back to the spreadsheet later.

If you want to compare this class to another class, calculate the same quartiles for the second class in a different column. The quartile values will tell you whether one class clustered higher or lower, and whether the spread was tighter or wider.

Understanding the difference in results between functions

With a large dataset (hundreds of rows), QUARTILE.INC and QUARTILE.EXC will give you nearly identical results — often within a fraction of a point. With a small dataset (under 30 rows), the difference becomes visible. This happens because the functions use different formulas to interpolate between your actual data points when the quartile falls between two values rather than landing exactly on one.

QUARTILE.INC includes the minimum (0) and maximum (4) as part of its calculation, so it treats your data as a closed set. QUARTILE.EXC excludes them, treating your data as a sample from a larger population. If you're unsure which to use, QUARTILE.INC is the safer default for most business situations — it's what most people expect when they ask for quartiles.

You can verify which function is right for your situation by checking what your audience or industry standard expects. If you're working with financial data, check whether your company's reporting guidelines specify inclusive or exclusive quartiles. If you're in a statistics class, your textbook will specify which method to use.

Frequently Asked Questions

What's the difference between QUARTILE and QUARTILE.INC?

QUARTILE and QUARTILE.INC are the same function — Excel renamed QUARTILE to QUARTILE.INC in newer versions to make it clearer that it uses the inclusive method. If you type QUARTILE in a modern version of Excel, it will still work, but QUARTILE.INC is the official current name.

Can I use these functions with text or non-numeric data?

No. QUARTILE, PERCENTILE, and all their variants only work with numbers. If your cells contain text, dates, or mixed content, Excel will ignore the text cells and calculate based only on the numeric values it finds.

What does it mean if QUARTILE returns a decimal when all my data is whole numbers?

The quartile value often falls between two actual data points in your set, so Excel calculates an interpolated value — a decimal that represents where the quartile boundary would be if you extended your data smoothly. This is normal and correct. You don't need to round it unless your specific use case requires whole numbers.

How do I find the interquartile range (IQR)?

The interquartile range is the distance between Q1 and Q3 — it shows the spread of the middle 50 percent of your data. Calculate it by subtracting Q1 from Q3. In a cell, type =QUARTILE.INC(A2:A51,3)-QUARTILE.INC(A2:A51,1) to get the IQR for your data range.

Why would I use PERCENTILE instead of QUARTILE?

PERCENTILE gives you more control. Quartiles only show you four points (25th, 50th, 75th percentiles). PERCENTILE lets you find any percentile — the 10th, 33rd, 90th, or any other value you need for your analysis. If you only need quartiles, QUARTILE is simpler; if you need custom percentiles, use PERCENTILE.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.