The interquartile range (IQR) in Excel is the difference between the third quartile (Q3) and the first quartile (Q1), measuring the spread of the middle 50% of a dataset. It's used to identify outliers and assess data variability without being skewed by extreme values.
IQR is calculated as Q3 minus Q1. In Excel, use the QUARTILE.EXC or QUARTILE.INC function to find these quartiles. For example, if your data is in cells A1:A100, enter =QUARTILE.EXC(A1:A100,3) for Q3 and =QUARTILE.EXC(A1:A100,1) for Q1. Subtract the second result from the first to get the IQR.
Excel's QUARTILE.EXC excludes the median when calculating quartiles, making it better for small datasets. QUARTILE.INC includes the median and is more common in educational contexts. Use QUARTILE.EXC unless instructed otherwise.
Interquartile range Excel identifies the central portion of a dataset, excluding the lowest and highest 25% of values. This makes it resistant to skew and ideal for datasets with outliers. For example, in a set of test scores ranging from 50 to 100, if Q1 is 65 and Q3 is 85, the IQR is 20 - meaning half the class scored between 65 and 85.
When you sort data, Q1 is the median of the lower half, and Q3 is the median of the upper half. Excel computes these automatically using internal algorithms based on position. The function uses either exclusive or inclusive methods depending on which variant you select.
For a dataset with 100 values, QUARTILE.EXC calculates Q1 at the 25th percentile, but skips the median in the calculation. This avoids bias in smaller samples. According to Microsoft's documentation, QUARTILE.EXC returns results consistent with ISO standards for percentile calculations.
Two primary functions calculate quartiles in Excel. Choose based on whether you want to exclude or include the median in the calculation.
| Function | Method | Use Case | Example Output |
|---|---|---|---|
QUARTILE.EXC |
Exclusive (excludes median) | Small datasets, statistical inference | Returns 25th percentile at position 25.25 in 100 values |
QUARTILE.INC |
Inclusive (includes median) | General analysis, education | Returns 25th percentile at position 25.00 in 100 values |
Using QUARTILE.EXC ensures consistency with academic standards like those in the NIST Engineering Statistics Handbook. It avoids overestimating the median in odd-numbered sets. For instance, with 99 values, QUARTILE.EXC treats the median as excluded from both halves.
Both functions return #NUM! if the range is empty or if quartile is less than 0 or greater than 4. Always validate input ranges before applying the formula.
Interquartile range Excel is widely used in finance, quality control, and social science research. It's key in box plots, where the box spans from Q1 to Q3, and the whiskers extend to min and max values within 1.5×IQR of the quartiles.
A common mistake is assuming IQR equals standard deviation. They measure different aspects: IQR captures spread in the middle 50%, while standard deviation reflects average distance from the mean. In a normal distribution, IQR ≈ 1.35 × standard deviation, but this varies with skew.
Another error is using QUARTILE instead of QUARTILE.EXC or QUARTILE.INC. The older QUARTILE function defaults to inclusive mode but lacks clarity. Use the newer versions for accuracy and compatibility.
Suppose you have monthly sales figures in column B (B2:B13) for a year: 120, 135, 140, 145, 150, 155, 160, 165, 170, 175, 180, 190.
=QUARTILE.EXC(B2:B13,1) in cell D1 to get Q1: 142.5.=QUARTILE.EXC(B2:B13,3) in cell D2 to get Q3: 172.5.=D2-D1 gives IQR = 30.This shows that the middle 50% of sales fell between $142.5K and $172.5K. Values below 142.5 - 1.5×30 = $105K or above 172.5 + 1.5×30 = $217.5K are flagged as potential outliers.
I've used this method in analyzing customer order data. When I applied IQR to delivery times, I found that 75% of orders arrived within 48 hours, but a few took over 7 days - these were flagged as outliers and traced to shipping delays.
Understanding interquartile range Excel improves your ability to interpret data distributions. Combine it with finding IQR in Excel for automated workflows. Use conditional formatting to show values beyond 1.5×IQR for quick visual detection.
Always verify that your data is sorted correctly before applying quartile functions. Excel does not sort automatically - unsorted data leads to incorrect quartile positions.
Final note: IQR is not affected by extreme values. In a dataset with one very high income, the IQR remains stable while the mean shifts. This resilience makes it essential for solid statistical reporting.