How to Find the Interquartile Range: Q1, Q3, Outliers, and Excel
The interquartile range is the most honest single number for “how spread out is the middle of this data?” It ignores the extremes entirely, measures the width of the middle 50%, and pairs with the median to describe a distribution without letting one wild value rewrite the story. Here is how to find it by hand, what its fences mean, and how to get it in Excel without a statistics package.
Key takeaways
- IQR = Q3 − Q1: the third quartile minus the first quartile, i.e. the width of the middle 50% of the data.
- Outlier fences: anything below Q1 − 1.5 × IQR or above Q3 + 1.5 × IQR is flagged as an outlier (Tukey’s rule). Flagged is not the same as wrong — investigate before deleting.
- Quartile methods differ slightly (inclusive vs exclusive medians); small datasets can give different Q1/Q3 by method, so state your method when it matters.
Finding the IQR by hand
Sort the data. Find the median (Q2): the middle value, or the average of the two middle values for an even-sized dataset. Then find Q1, the median of the lower half, and Q3, the median of the upper half. The IQR is Q3 − Q1. The subtlety is what counts as the “lower half” when the dataset has an odd size: the exclusive method (Tukey’s original) excludes the overall median from both halves, while the inclusive method includes it in both. For large datasets the difference is negligible; for small ones it can shift Q1 and Q3 by a step, so note your method if anyone will reproduce your work.
The interquartile range calculator on this page computes Q1, Q3, and the IQR from any list, and shows the sorted data so you can verify the halves yourself.
Worked example, start to finish
Data (already sorted): 12, 15, 18, 21, 24, 27, 30, 33, 36, 39, 42. Eleven values. Median (6th value): 27. Lower half (exclusive method, excluding the median): 12, 15, 18, 21, 24 — median Q1 = 18. Upper half: 30, 33, 36, 39, 42 — median Q3 = 36. IQR = 36 − 18 = 18. The middle 50% of the data spans 18 units, from 18 to 36.
A second example with an even count: 4, 7, 9, 12, 15, 18, 22, 26. Median = (12 + 15)/2 = 13.5. Lower half: 4, 7, 9, 12 — Q1 = (7 + 9)/2 = 8. Upper half: 15, 18, 22, 26 — Q3 = (18 + 22)/2 = 20. IQR = 20 − 8 = 12. Notice the median itself (13.5) is not a data value — medians of even sets are averages, and that is fine; quartiles are positions in the distribution, not necessarily observed values.
Outlier fences: the 1.5 × IQR rule
Tukey’s fences flag values far from the middle: lower fence = Q1 − 1.5 × IQR, upper fence = Q3 + 1.5 × IQR. For the first example: lower fence = 18 − 27 = −9, upper fence = 36 + 27 = 63. Any value below −9 or above 63 would be flagged. A second, “far out” fence at 3 × IQR marks extreme outliers. Box plots draw exactly these fences: the box spans Q1 to Q3, the line inside is the median, and points beyond the fences are plotted individually as dots.
Flagged does not mean deletable. An outlier can be a data-entry error (a misplaced decimal), a measurement failure, or a genuine extreme — a billionaire in an income sample, a record heatwave in a temperature series. Delete errors, investigate failures, and keep genuine extremes while reporting analyses with and without them. Deleting an outlier because it is inconvenient is how honest datasets become misleading ones.
IQR vs standard deviation
| IQR | Standard deviation | |
|---|---|---|
| Pairs with | Median | Mean |
| Uses which data? | Middle 50% only | Every value |
| Effect of one wild value | None (unless it shifts quartiles) | Large |
| Best for | Skewed data, outliers present | Symmetric, roughly normal data |
| Describes | Spread of the typical range | Typical distance from the mean |
Income, house prices, and waiting times are usually skewed with long right tails — the IQR and median describe them honestly, while the mean and standard deviation are dragged upward by the tail. Test scores and measurement errors are often symmetric, where the standard deviation earns its keep. Reporting both pairs, when space allows, is better than arguing about which is “right”.
IQR in Excel
Excel has no single IQR function, but QUARTILE.INC and QUARTILE.EXC give Q1 and Q3 directly: IQR = QUARTILE.INC(range, 3) − QUARTILE.INC(range, 1). The .INC version uses the inclusive method (matching the “inclusive” quartile definition); .EXC uses the exclusive method. For the first example above, QUARTILE.INC gives Q1 = 19.5 and Q3 = 34.5 (IQR 15) under the inclusive method, versus 18 and 36 (IQR 18) under the exclusive method — a concrete illustration of why the method matters on small datasets. For the outlier fences, compute Q1 − 1.5 × IQR and Q3 + 1.5 × IQR in adjacent cells, then use conditional formatting or a helper column to flag values outside them. Google Sheets uses the same QUARTILE.INC/QUARTILE.EXC functions.
Frequently asked questions
How do you find the interquartile range?
Sort the data, find the median, then find Q1 (median of the lower half) and Q3 (median of the upper half). IQR = Q3 − Q1. For 12, 15, 18, 21, 24, 27, 30, 33, 36, 39, 42: Q1 = 18, Q3 = 36, IQR = 18.
What is the 1.5 × IQR rule?
Tukey’s outlier rule: values below Q1 − 1.5 × IQR or above Q3 + 1.5 × IQR are flagged as outliers. A 3 × IQR fence marks extreme outliers. Flagged values should be investigated, not automatically deleted.
Why do different methods give different IQRs?
Because quartile definitions differ on whether the overall median is included in the halves (inclusive vs exclusive) and on interpolation. Small datasets show the biggest differences; large datasets converge. State your method when reporting.
When should I use IQR instead of standard deviation?
When data are skewed or contain outliers — income, house prices, waiting times. The IQR and median resist extremes; the mean and standard deviation do not. For symmetric, outlier-free data, the standard deviation is fine.
What does the IQR tell me that the range does not?
The range (max − min) is set by the two most extreme values and can be huge from one wild point. The IQR measures the middle 50% and is stable: it describes the typical spread, not the extremes.
How do I calculate IQR in Excel?
Use =QUARTILE.INC(range,3) − QUARTILE.INC(range,1) for the inclusive method, or QUARTILE.EXC for the exclusive method. There is no built-in IQR function.
Find Q1, Q3, and the IQR for your data
Run your own numbers instead of guessing from someone else’s example.