Yes, you can calculate interquartile range in Excel with two QUARTILE functions subtracted. But if you’re using QUARTILE (legacy) or mixing .INC and .EXC without checking your dataset size, your IQR is silently wrong — and your outlier analysis is broken.
Quick Answer
The correct way to do interquartile range in Excel is: =QUARTILE.EXC(B2:B15,3) - QUARTILE.EXC(B2:B15,1). Use .EXC for sample-based IQR (most common), avoid the old QUARTILE, and never mix .INC and .EXC — they use different percentile definitions and will give inconsistent results.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| QUARTILE.EXC + subtraction | Enter =QUARTILE.EXC(A2:A16,3)-QUARTILE.EXC(A2:A16,1) | Most real-world datasets (n ≥ 8) | Fails on small samples (n < 4); returns #NUM! for n=4–7 |
| QUARTILE.INC + subtraction | Enter =QUARTILE.INC(A2:A16,3)-QUARTILE.INC(A2:A16,1) | Teaching contexts or when matching textbook formulas | Includes min/max in quartile calc → wider IQR than standard practice |
| Manual median split + MEDIAN() | Sort data → split at median → apply MEDIAN() to lower/upper halves | Small datasets where transparency matters (e.g., audit-ready reports) | Tedious beyond ~20 rows; breaks if data changes |
| Power Query + custom column | Load data → Advanced Editor → add =List.Quartiles(#"Changed Type"[Sales], 3) - List.Quartiles(#"Changed Type"[Sales], 1) | Teams refreshing IQR weekly from live DB exports | Requires Power Query knowledge; no dynamic cell reference |
| Array formula with PERCENTILE.EXC | Enter =PERCENTILE.EXC(A2:A16,0.75)-PERCENTILE.EXC(A2:A16,0.25) (Ctrl+Shift+Enter not needed) | Consistency with statistical software (R, Python) | Same n ≥ 8 limit as QUARTILE.EXC; less intuitive naming |
Method 1 Deep Dive
Let’s walk through QUARTILE.EXC using real sales data from Alibaba’s Q1 channel partners:
| Partner | Q1 Sales ($) |
|---|---|
| Sarah Chen | $32,450 |
| Acme Corp | $45,200 |
| Nexus Ltd | $28,750 |
| Terra Imports | $51,900 |
| Orion Group | $37,120 |
| Vista Trading | $42,800 |
| Stellar Inc | $29,300 |
| Zephyr Co | $56,400 |
| Luna Distributors | $34,650 |
| Kepler Solutions | $48,200 |
Data lives in B2:B11 (10 values). First, sort it mentally or with Alt + A + S + S (Data tab → Sort → Smallest to Largest). Then compute:
- Q1:
=QUARTILE.EXC(B2:B11,1)→ $32,450 - Q3:
=QUARTILE.EXC(B2:B11,3)→ $48,200 - IQR:
=B13-B12(if Q1 is in B12, Q3 in B13) → $15,750
Here’s the counterintuitive part: QUARTILE.EXC excludes 0% and 100% percentiles — so with only 10 points, it interpolates between actual values. That’s why QUARTILE.INC gives $16,275 here. Neither is “wrong”, but .EXC matches how R’s IQR() and most stats textbooks define it.
Method 2 Deep Dive
When your boss asks “Can you show *exactly* how you got Q1?” — go manual. This is what we did last month for a compliance review with Alibaba’s finance team.
Step 1: Sort B2:B11 ascending (Alt + A + S + S). You get: $28,750, $29,300, $32,450, $34,650, $37,120, $42,800, $45,200, $48,200, $51,900, $56,400.
Step 2: Find median = average of 5th & 6th = ($37,120 + $42,800)/2 = $39,960.
Step 3: Lower half = first 5 values → $28,750 to $37,120. Median of that = Q1 = $32,450.
Step 4: Upper half = last 5 values → $42,800 to $56,400. Median = Q3 = $48,200.
IQR = $48,200 − $32,450 = $15,750. Same result — but now you can paste the sorted list into your report and highlight each step. Bonus: this method works even if your version of Excel lacks .EXC (pre-2010).
Cheat Sheet
| Task | Formula / Shortcut | Notes |
|---|---|---|
| Calculate Q1 (exclusive) | =QUARTILE.EXC(B2:B11,1) | Use for sample IQR; fails if n < 4 |
| Calculate Q3 (exclusive) | =QUARTILE.EXC(B2:B11,3) | Always pair with same function — never mix .INC/.EXC |
| Full IQR in one cell | =QUARTILE.EXC(B2:B11,3)-QUARTILE.EXC(B2:B11,1) | No Ctrl+Shift+Enter needed — it’s not an array formula |
| Sort data fast | Alt + A + S + S | Assumes active cell is inside data range |
| Check data count | =COUNT(B2:B11) | Must be ≥ 8 for stable .EXC results |
| Fallback for tiny datasets | =MEDIAN(B2:B6)-MEDIAN(B7:B11) (after sorting) | Only works if n is even and you split cleanly at median |
