Aaron Graves, PhDude (Replica)

Empowering people to reach their full potential

How to Calculate the Interquartile Range (IQR) in Excel

Posted on February 16, 2026 by Admin

IQR Calculator

IQR: -

Q1: -

Q3: -

(Uses a common linear interpolation method for quartiles)

Understanding the Interquartile Range (IQR)

The Interquartile Range (IQR) is a measure of statistical dispersion, representing the spread of the middle 50% of a dataset. It's often used in conjunction with the median to describe the distribution of data, especially when the data is skewed or contains outliers, as it's less sensitive to extreme values than the standard deviation.

In simple terms, the IQR is the difference between the third quartile (Q3) and the first quartile (Q1). Q1 represents the 25th percentile of the data, meaning 25% of the data falls below this value. Q3 represents the 75th percentile, meaning 75% of the data falls below this value.

Why is IQR Important?

  • Measures Spread: It tells you how spread out the central portion of your data is.
  • Identifies Outliers: Data points that fall below Q1 - 1.5 * IQR or above Q3 + 1.5 * IQR are often considered outliers.
  • Robust to Extremes: Unlike range or standard deviation, IQR is not affected by extreme values, making it ideal for skewed distributions.

Calculating IQR in Excel: The Easy Way

Excel provides powerful functions to calculate quartiles, which are the building blocks for IQR. There are two primary functions you'll use: QUARTILE.EXC and QUARTILE.INC. The choice between them depends on how you want to handle the inclusion or exclusion of the median in quartile calculations, particularly for smaller datasets.

Method 1: Using the QUARTILE.EXC Function (Exclusive)

The QUARTILE.EXC function calculates quartiles exclusively, meaning it excludes the median from the calculation of Q1 and Q3, especially when the number of data points is odd. This is generally the more common statistical method for many applications.

Steps:

  1. Enter Your Data: Input your dataset into a column or row in Excel. For example, let's use the numbers: 1, 3, 5, 7, 9, 11, 13, 15, 17, 19 in cells A1:A10.
  2. Calculate Q1 (First Quartile): In an empty cell, type the formula:
    =QUARTILE.EXC(A1:A10, 1)
    This will return the value for the 25th percentile.
  3. Calculate Q3 (Third Quartile): In another empty cell, type the formula:
    =QUARTILE.EXC(A1:A10, 3)
    This will return the value for the 75th percentile.
  4. Calculate IQR: Subtract Q1 from Q3. In a third cell, type:
    =[Cell with Q3] - [Cell with Q1]
    For our example, if Q1 is in B1 and Q3 is in B2, you'd use =B2-B1.

Example:

For the data 1, 3, 5, 7, 9, 11, 13, 15, 17, 19:

  • =QUARTILE.EXC(A1:A10, 1) would yield 5.5
  • =QUARTILE.EXC(A1:A10, 3) would yield 14.5
  • IQR = 14.5 - 5.5 = 9

Method 2: Using the QUARTILE.INC Function (Inclusive)

The QUARTILE.INC function calculates quartiles inclusively, meaning it includes the median in the calculation of Q1 and Q3. This method is often used in descriptive statistics and is the default behavior of the older QUARTILE function.

Steps:

  1. Enter Your Data: Use the same dataset as before, e.g., 1, 3, 5, 7, 9, 11, 13, 15, 17, 19 in cells A1:A10.
  2. Calculate Q1 (First Quartile): In an empty cell, type the formula:
    =QUARTILE.INC(A1:A10, 1)
  3. Calculate Q3 (Third Quartile): In another empty cell, type the formula:
    =QUARTILE.INC(A1:A10, 3)
  4. Calculate IQR: Subtract Q1 from Q3.

Example:

For the data 1, 3, 5, 7, 9, 11, 13, 15, 17, 19:

  • =QUARTILE.INC(A1:A10, 1) would yield 5.5
  • =QUARTILE.INC(A1:A10, 3) would yield 14.5
  • IQR = 14.5 - 5.5 = 9

Note: For datasets with an even number of observations, QUARTILE.EXC and QUARTILE.INC often yield the same results. Differences are more apparent with odd-sized datasets.

Method 3: Manual Calculation (For Understanding)

While Excel functions are convenient, understanding the manual process helps solidify your grasp of IQR.

Steps:

  1. Sort Your Data: Arrange your dataset in ascending order.
  2. Find the Median (Q2): This is the middle value of your entire dataset. If you have an even number of data points, it's the average of the two middle values.
  3. Find Q1 (First Quartile): This is the median of the lower half of your sorted data.
    • If the total number of data points (n) is odd, exclude the overall median when finding the lower half.
    • If n is even, divide the data exactly in half.
  4. Find Q3 (Third Quartile): This is the median of the upper half of your sorted data.
    • Similar to Q1, exclude the overall median if n is odd.
    • If n is even, divide the data exactly in half.
  5. Calculate IQR: Subtract Q1 from Q3 (IQR = Q3 - Q1).

Example:

Let's use an odd-sized dataset: 1, 2, 3, 4, 5, 6, 7

  1. Sorted Data: 1, 2, 3, 4, 5, 6, 7
  2. Median (Q2): 4 (the middle value)
  3. Lower Half: 1, 2, 3 (excluding the median 4)
  4. Q1: Median of the lower half = 2
  5. Upper Half: 5, 6, 7 (excluding the median 4)
  6. Q3: Median of the upper half = 6
  7. IQR: Q3 - Q1 = 6 - 2 = 4

Note: This manual method typically aligns with the QUARTILE.EXC function for odd datasets and QUARTILE.INC for even datasets, depending on how you define the halves. Excel's functions use more precise interpolation methods.

Choosing Between QUARTILE.EXC and QUARTILE.INC

  • QUARTILE.EXC (Exclusive): Generally preferred for statistical analysis, especially when identifying outliers using the 1.5 * IQR rule. It excludes the median from the lower and upper halves when calculating Q1 and Q3, which can be more robust for odd-sized datasets.
  • QUARTILE.INC (Inclusive): More aligned with older methods and some textbook definitions. It includes the median in the lower and upper halves when the dataset size is odd. For even-sized datasets, the results are often identical to QUARTILE.EXC.

For most practical purposes, especially with larger datasets, the difference between the two functions is negligible. If you're unsure, QUARTILE.EXC is often the recommended choice in modern statistical contexts.

Interpreting Your IQR Result

Once you have the IQR, what does it tell you? A small IQR indicates that the central 50% of your data is tightly clustered around the median. A large IQR suggests that the middle 50% of your data is more spread out. This can be crucial for understanding data variability and consistency.

For example, if you're analyzing test scores, a low IQR means most students scored similarly, while a high IQR suggests a wide range of scores among the majority of students.

Conclusion

Calculating the Interquartile Range in Excel is straightforward using the QUARTILE.EXC or QUARTILE.INC functions. By understanding what IQR represents and how to compute it, you gain a valuable tool for analyzing data distribution, identifying variability, and detecting potential outliers in your datasets. Whether for academic, business, or personal data analysis, mastering IQR is a fundamental step towards better data interpretation.