Written and maintained by CASRAI Editorial Board
Last updated
To calculate the IQR in Excel, subtract the first quartile from the third: =QUARTILE.INC(A2:A9,3)-QUARTILE.INC(A2:A9,1), with your own range in place of A2:A9. The interquartile range is the width of the middle half of your data, and Excel has no single IQR function, so you build it from two quartile calls. The choice that changes your answer is QUARTILE.INC versus QUARTILE.EXC: on the same five numbers they give an IQR of 4 and 6, and this guide shows both with the working, so you can state which one you used.
What the IQR is used for in research
The IQR is the spread statistic usually reported next to the median for skewed measurements, and it is the basis of the 1.5 × IQR rule for flagging outliers on a box plot. For how to report the pair, see median and interquartile range; for the box plot itself, see box plots and the IQR. If you only need the median cell, see how to calculate the median in Excel.
Install or access: nothing to install
Quartiles come from built-in worksheet functions, so there is no add-in to enable. Microsoft documents three of them: QUARTILE.INC, QUARTILE.EXC and the older QUARTILE. A broader list of built-in statistics functions is in Excel statistics functions for research.
How do I calculate Q1 in Excel?
Type =QUARTILE.INC(A2:A9,1) in an empty cell and press Enter. The second argument selects which quartile you want. Per Microsoft Support, the syntax is QUARTILE.INC(array,quart), where array is the range of numeric values and quart is the value to return:
| quart | Returns |
|---|---|
| 0 | Minimum value |
| 1 | First quartile (25th percentile) |
| 2 | Median (50th percentile) |
| 3 | Third quartile (75th percentile) |
| 4 | Maximum value |
Microsoft’s own worked example uses 1, 2, 4, 7, 8, 9, 10, 12 in A2:A9, and =QUARTILE.INC(A2:A9,1) returns 3.5. A non-integer quart is truncated, and an empty array or a quart below 0 or above 4 returns #NUM!.
IQR formula in Excel: step by step
- Enter your numbers in one column, for example A2:A9.
- In an empty cell (say C2) type
=QUARTILE.INC(A2:A9,1)for Q1. - In the next cell (C3) type
=QUARTILE.INC(A2:A9,3)for Q3. - In a third cell (C4) type
=C3-C2. That cell is the IQR. - Or do it in one cell:
=QUARTILE.INC(A2:A9,3)-QUARTILE.INC(A2:A9,1).
Checked by hand on Microsoft’s example data (1, 2, 4, 7, 8, 9, 10, 12): Q1 is 3.5 (Microsoft’s published result), Q3 is 9.25 (the third quartile sits at position 1 + 0.75 × 7 = 6.25, a quarter of the way from 9 to 10), so the IQR is 9.25 − 3.5 = 5.75. The same formula answers the searches phrased as how to find the interquartile range in Excel, the interquartile range formula in Excel, or Excel IQR: there is one approach, written two ways.
What is the interquartile range of 2, 4, 6, 8, 10?
The interquartile range of 2, 4, 6, 8, 10 is 4 under QUARTILE.INC (the method Excel’s older QUARTILE uses) and 6 under QUARTILE.EXC. Working, with five values sorted and n = 5:
- Inclusive: the position of the p-th quartile is 1 + p(n − 1). Q1 is at position 2, so Q1 = 4. Q3 is at position 4, so Q3 = 8. IQR = 8 − 4 = 4.
- Exclusive: the position is (n + 1)p. Q1 is at position 1.5, halfway between 2 and 4, so Q1 = 3. Q3 is at position 4.5, halfway between 8 and 10, so Q3 = 9. IQR = 9 − 3 = 6.
Both answers are correct for their definition. The two functions are two accepted quartile definitions, not a right and a wrong one.
QUARTILE.INC vs QUARTILE.EXC vs QUARTILE
| Function | Valid quart values | IQR of 2, 4, 6, 8, 10 | Source note |
|---|---|---|---|
QUARTILE.INC |
0, 1, 2, 3, 4 | 4 | Percentile values 0 to 1, inclusive |
QUARTILE.EXC |
1, 2, 3 | 6 | Percentile values 0 to 1, exclusive; quart of 0 or 4 returns #NUM! |
QUARTILE |
0 to 4 | 4 (same as INC) | Compatibility function; Microsoft says it may not be available in future versions and recommends QUARTILE.EXC and QUARTILE.INC |
Microsoft’s QUARTILE.EXC example uses 6, 7, 15, 36, 39, 40, 41, 42, 43, 47, 49 in A2:A12: =QUARTILE.EXC(A2:A12,1) returns 15 and =QUARTILE.EXC(A2:A12,3) returns 43, so the exclusive IQR is 43 − 15 = 28. The practical rule: pick one function, use it for every variable in the study, and name it in the methods section. If a reviewer recomputes your IQR in R, SPSS or by hand, a different quartile definition is the first thing to check; percentiles and quartiles explains the definitions behind the differences.
How to run median and IQR in Excel
Put the median and the two quartiles in adjacent cells so the pair can be copied straight into a results table:
- Median:
=MEDIAN(A2:A9)(Microsoft notesMEDIANequalsQUARTILE.INCat quart 2, so=QUARTILE.INC(A2:A9,2)gives the same value) - Q1:
=QUARTILE.INC(A2:A9,1) - Q3:
=QUARTILE.INC(A2:A9,3)
Report it as the median with Q1 and Q3 (for the example data, median 7.5, Q1 3.5, Q3 9.25), or as the median with the IQR width, and say which quartile method was used.
How do you find Q1, Q2, Q3 and the interquartile range?
Q1 is QUARTILE.INC(range,1), Q2 is QUARTILE.INC(range,2) (the median), Q3 is QUARTILE.INC(range,3), and the IQR is Q3 minus Q1. Swap in QUARTILE.EXC throughout if your field or software convention uses the exclusive definition.
Output and how to read it
The IQR is a single number in the same units as your data. It is the distance covered by the middle 50% of the values, so a larger IQR means a wider spread and it is not moved by a few extreme values. A common convention (the Tukey rule behind most box plots) marks values below Q1 − 1.5 × IQR or above Q3 + 1.5 × IQR as potential outliers. This is a statistical convention, not an Excel feature. With Q1 in C2 and Q3 in C3, the fences are:
- Lower fence:
=C2-1.5*(C3-C2) - Upper fence:
=C3+1.5*(C3-C2) - Flag a value in A2:
=OR(A2<lower_fence_cell,A2>upper_fence_cell)
Flagged values are candidates for checking, not for automatic deletion; see how to find an outlier in statistics before removing anything.
Troubleshooting
#NUM!fromQUARTILE.INC: the array is empty orquartis below 0 or above 4.#NUM!fromQUARTILE.EXC: the array is empty, orquartis 0, 4 or outside 1 to 3. UseQUARTILE.INCif you need the minimum or maximum.- Your IQR differs from another program: compare quartile definitions first. Excel gives you both an inclusive and an exclusive version, and other software may default to a different one.
- Result looks wrong on a column that includes labels: point the range at the numeric cells only, so the header row is not part of the array.
Free alternatives and cross-checks
| Method | When to use it |
|---|---|
| By hand (sorted values and the position rules above) | To verify a small dataset, such as the five-number example |
| R (open-source), see how to calculate the IQR in R | To reproduce the result in a script and see which quartile type it uses |
For the conceptual definition and the five-number summary, see the interquartile range and five-number summary.
FAQ
How to calculate the IQR formula?
IQR = Q3 − Q1. In Excel that is =QUARTILE.INC(range,3)-QUARTILE.INC(range,1).
How do I calculate Q1 in Excel?
Use =QUARTILE.INC(range,1), or =QUARTILE.EXC(range,1) for the exclusive definition.
How to run median and IQR in Excel?
Use =MEDIAN(range) plus the two QUARTILE.INC cells for Q1 and Q3, then subtract for the IQR.
What is the interquartile range of 2, 4, 6, 8, 10?
It is 4 with QUARTILE.INC (Q1 = 4, Q3 = 8) and 6 with QUARTILE.EXC (Q1 = 3, Q3 = 9).
How do you find Q1, Q2, Q3, and interquartile range?
Call the quartile function with quart 1, 2 and 3, then subtract Q1 from Q3 for the IQR.
Which function should I use, QUARTILE.INC or QUARTILE.EXC?
Either is valid; choose one, apply it consistently, and state it. QUARTILE.INC matches the older QUARTILE function and accepts 0 to 4.
Sources
- Microsoft Support, QUARTILE.INC function (checked 2026-10-29).
- Microsoft Support, QUARTILE.EXC function (checked 2026-10-29).
- Microsoft Support, QUARTILE function (checked 2026-10-29).
