What quartiles are and why Excel can find them for you
A quartile is a number 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, 50 percent above. The third quartile (Q3) marks where 75 percent falls below. Excel can calculate all three using the QUARTILE function, which takes your data range and tells it which quartile you want.
You use quartiles when you need to understand where values cluster or spot outliers. If you have 100 test scores, quartiles show you the range where the bottom quarter scored, where the middle half scored, and where the top quarter scored. Excel does the sorting and math for you — you just point it at your data and ask for Q1, Q2, or Q3.
Key Takeaways
- The QUARTILE function in Excel takes two inputs: your data range and a number from 0 to 4 that tells Excel which quartile to return.
- Q1 (quartile 1) shows the value where 25 percent of your data falls below; Q3 shows where 75 percent falls below; Q2 is the median.
- You can also use QUARTILE.INC or QUARTILE.EXC depending on whether you want to include or exclude the minimum and maximum values in the calculation.
- Quartiles work on numeric data only — Excel will ignore text, blank cells, and logical values like TRUE or FALSE.
Using the QUARTILE function with your data
Open your spreadsheet and arrange your data in a single column or row. The data must be numbers — Excel cannot calculate quartiles on text. For example, if your sales figures are in cells A2 through A51, you have 50 data points to work with.
Click on an empty cell where you want the result to appear. Type the formula =QUARTILE(A2:A51,1) to find the first quartile. The first number in parentheses is your data range. The second number tells Excel which quartile: use 1 for Q1, 2 for Q2 (the median), or 3 for Q3. Press Enter and Excel returns the value.
If you want all three quartiles at once, create three separate formulas in three cells. Use =QUARTILE(A2:A51,1) in one cell, =QUARTILE(A2:A51,2) in another, and =QUARTILE(A2:A51,3) in a third. This gives you a quick picture of how your data spreads across the four quarters.
Understanding QUARTILE.INC versus QUARTILE.EXC
Excel offers two versions of the quartile function, and they calculate slightly differently. QUARTILE.INC (inclusive) includes the minimum and maximum values of your dataset in its calculation. QUARTILE.EXC (exclusive) excludes them. For most everyday use, QUARTILE.INC is the standard — it is what the plain QUARTILE function does.
The difference matters most when your dataset is small. With 50 or more data points, both methods produce nearly identical results. With 10 data points, you may see a noticeable difference. If you are following a textbook or a specific methodology, check which version it calls for. Otherwise, QUARTILE.INC is the safe choice.
To use the exclusive version, type =QUARTILE.EXC(A2:A51,1) instead. The syntax is identical to QUARTILE — only the function name changes. Both versions are available in Excel 2010 and later.
What happens when your data has gaps or unusual values
Excel ignores blank cells, text, and logical values (TRUE, FALSE) when calculating quartiles. If your range is A2:A51 but some cells are empty, Excel counts only the numeric values. This is usually what you want — it prevents blanks from throwing off your calculation.
If a cell contains an error (like #DIV/0!), the entire formula fails and returns an error. Check your data for errors before running QUARTILE. Negative numbers are fine — Excel treats them as regular values and includes them in the calculation.
Duplicate values do not cause problems. If your dataset has the same number repeated many times, Excel counts each instance separately. This is correct behavior — duplicates are real data points and belong in the calculation.
Visualizing quartiles with a straightforward table
| Quartile | What it means | Excel formula |
|---|---|---|
| Q1 (First quartile) | 25 percent of data falls below this value | =QUARTILE(range,1) |
| Q2 (Second quartile) | 50 percent of data falls below this value; also called the median | =QUARTILE(range,2) |
| Q3 (Third quartile) | 75 percent of data falls below this value | =QUARTILE(range,3) |
| Q0 and Q4 | Q0 returns the minimum value; Q4 returns the maximum value | =QUARTILE(range,0) or =QUARTILE(range,4) |
A worked example with real numbers
Imagine you have 10 test scores: 55, 62, 68, 71, 75, 78, 82, 85, 90, 95. You put these in cells A1 through A10. To find Q1, you click on cell B1 and type =QUARTILE(A1:A10,1). Excel returns 67.25. This means 25 percent of the scores fall below 67.25.
For Q2 (the median), you type =QUARTILE(A1:A10,2) in cell B2. Excel returns 76.5 — the middle point of your data. For Q3, you type =QUARTILE(A1:A10,3) in cell B3. Excel returns 86.25, meaning 75 percent of scores fall below this value. Now you can see at a glance that the bottom quarter scored in the 55–67 range, the middle half scored 67–86, and the top quarter scored 86–95.
Frequently Asked Questions
Can I calculate quartiles on data in multiple columns?
No — QUARTILE works on a single range only. If your data spans multiple columns, copy it into a single column first, or run separate QUARTILE formulas for each column. Excel will not automatically combine data from different columns into one calculation.
What is the difference between quartiles and percentiles?
Quartiles divide data into four parts (25, 50, 75 percent). Percentiles divide data into 100 parts. Excel has a PERCENTILE function for that. If you need the 90th percentile, use =PERCENTILE(range,0.9). Quartiles are simpler when you only need to split data into quarters.
Why does my QUARTILE formula return a decimal when my data is all whole numbers?
Excel calculates quartiles by interpolating between values, not by picking an exact data point. If the quartile falls between two numbers in your dataset, Excel returns a decimal. This is mathematically correct — it represents the true position of that quartile.
Can I use QUARTILE on data sorted in descending order?
Yes — QUARTILE does not care about sort order. It reads all the values in your range and calculates the quartile regardless of whether they are sorted ascending, descending, or randomly. The result is the same either way.
What if I only have a few data points — does QUARTILE still work?
Yes, but the result is less meaningful. Quartiles are designed for larger datasets where dividing into four parts makes sense. With only five data points, each quarter contains 1.25 points, and the quartile values may not feel intuitive. For small datasets, consider looking at the minimum, maximum, and median instead.