How to Find Interquartile Range in Excel
Finding the interquartile range (IQR) is a fundamental skill for anyone working with statistical data in Excel. The IQR measures the spread of the middle 50 % of your dataset, making it a reliable indicator of variability that is less affected by extreme values. In this article you will learn the concept behind the IQR, the exact steps to calculate it using built‑in Excel functions, and tips for avoiding common pitfalls. By the end, you’ll be able to compute the IQR quickly and interpret the results with confidence.
Understanding the Interquartile Range
What is the Interquartile Range?
The interquartile range is defined as the difference between the third quartile (Q3) and the first quartile (Q1) Worth keeping that in mind..
- Q1 (the first quartile or 25th percentile) marks the value below which 25 % of the data falls.
- Q3 (the third quartile or 75th percentile) marks the value below which 75 % of the data falls.
Mathematically,
[ \text{IQR} = Q3 - Q1 ]
The IQR is especially useful because it focuses on the central portion of the data, ignoring the highest and lowest 25 % of observations.
Why Use the IQR?
- Robustness: Unlike the range or standard deviation, the IQR is not heavily influenced by outliers.
- Interpretation: It tells you how spread out the middle half of your data is, which is helpful for box‑plot construction and for comparing distributions.
Step‑by‑Step Guide to Calculate IQR in Excel
Below is a clear, numbered process you can follow regardless of your Excel version (Excel 2016, 2019, Microsoft 365, or Excel for Mac).
-
Prepare Your Data
- Place your dataset in a single column or row.
- Ensure there are no blank cells within the selected range; if there are, Excel will ignore them, but it’s cleaner to remove them first.
-
Sort the Data (Optional but Recommended)
- Sorting helps you visually verify the distribution and makes it easier to spot errors.
- Use the Data tab → Sort to arrange the values in ascending order.
-
Identify the Quartile Positions
- Excel offers two main functions for quartiles: QUARTILE.INC (inclusive) and QUARTILE.EXC (exclusive).
- For most practical purposes, QUARTILE.INC is preferred because it includes the median in the calculation, matching the common definition of quartiles.
-
Calculate Q1 and Q3
- Q1:
=QUARTILE.INC(range, 1) - Q3:
=QUARTILE.INC(range, 3)
Example: If your data is in cells A2:A21, the formulas become:
- Q1:
=QUARTILE.INC(A2:A21, 1) - Q3:
=QUARTILE.INC(A2:A21, 3)
- Q1:
-
Compute the IQR
- Subtract Q1 from Q3:
=Q3 - Q1 - You can combine the steps into a single formula:
=QUARTILE.INC(A2:A21, 3) - QUARTILE.INC(A2:A21, 1)
- Subtract Q1 from Q3:
-
Verify the Result
- Cross‑check by creating a box plot (Insert → Chart → Box & Whisker). The box in the chart spans from Q1 to Q3, and the distance between them visually represents the IQR.
Using Excel Functions Effectively
QUARTILE.INC vs. QUARTILE.EXC
- QUARTILE.INC uses a linear interpolation method that includes the median when calculating quartiles. This is the default in many statistical textbooks.
- QUARTILE.EXC excludes the median and is useful when you need a strict exclusive quartile definition, but it can produce errors if the dataset size is small (less than 5).
Alternative Functions
- MEDIAN (
=MEDIAN(range)) helps you locate the center value, which is the basis for splitting data into lower and upper halves. - PERCENTILE.INC (
=PERCENTILE.INC(range, k)) can also be used: set k to 0.25 for Q1 and 0.75 for Q3. The result is identical to QUARTILE.INC but expressed as a percentile.
Dynamic Arrays (Excel 365)
If you are using the dynamic array version of Excel, you can spill the quartile values across cells automatically:
=LET(
data, A2:A21,
q1, QUARTILE.INC(data, 1),
q3, QUARTILE.INC(data, 3),
iqr, q3 - q1,
"Q1 = " & q1 & ", Q3 = " & q3 & ", IQR = " & iqr
)
This single formula returns a readable text string with all three values.
Manual Calculation (For Learning Purposes)
Sometimes it’s beneficial to calculate the IQR manually to understand the underlying mechanics. Follow these steps:
- Sort the data in ascending order.
- Find the median (Q2) – the middle value of the