Aaron Graves, PhDude (Replica)

Empowering people to reach their full potential

how to calculate iqr in excel

Posted on February 16, 2026 by Admin
Enter numbers and click 'Calculate IQR' to see results.

The Interquartile Range (IQR) is a crucial measure of statistical dispersion, representing the spread of the middle 50% of a dataset. It's particularly useful for identifying outliers and understanding the variability of data without being influenced by extreme values, unlike the total range. If you're working with data in Excel, calculating the IQR is straightforward with a few powerful functions. This guide will walk you through various methods to calculate IQR in Excel, including practical examples.

What is the Interquartile Range (IQR)?

The IQR is the difference between the third quartile (Q3) and the first quartile (Q1). These quartiles divide a dataset into four equal parts:

  • Q1 (First Quartile): The 25th percentile, meaning 25% of the data falls below this value.
  • Q2 (Second Quartile): The 50th percentile, which is also the median of the dataset.
  • Q3 (Third Quartile): The 75th percentile, meaning 75% of the data falls below this value.

In essence, IQR = Q3 - Q1. A smaller IQR indicates that the central 50% of the data is tightly clustered, while a larger IQR suggests greater variability.

Method 1: Using the QUARTILE.INC Function

The QUARTILE.INC function in Excel calculates the quartile of a dataset based on a percentile range from 0 to 1, inclusive. This method is commonly used and aligns with how many textbooks define quartiles.

Syntax:

=QUARTILE.INC(array, quart)

  • array: The range of cells containing your numeric data.
  • quart: The quartile you want to find.
    • 0 for the minimum value
    • 1 for the first quartile (Q1)
    • 2 for the median (Q2)
    • 3 for the third quartile (Q3)
    • 4 for the maximum value

Example:

Let's say your data is in cells A1:A10: 10, 12, 15, 18, 20, 22, 25, 28, 30, 32

  1. To find Q1: Enter =QUARTILE.INC(A1:A10, 1).
  2. To find Q3: Enter =QUARTILE.INC(A1:A10, 3).
  3. To calculate IQR: Subtract Q1 from Q3. If Q1 is in cell C1 and Q3 is in cell C2, enter =C2-C1.

Step-by-Step:

  1. Enter your data into a column (e.g., A1:A10).
  2. In an empty cell, type =QUARTILE.INC(A1:A10, 1) and press Enter to get Q1.
  3. In another empty cell, type =QUARTILE.INC(A1:A10, 3) and press Enter to get Q3.
  4. In a third empty cell, subtract the Q1 value from the Q3 value (e.g., if Q1 is in B1 and Q3 in B2, type =B2-B1).

Method 2: Using the QUARTILE.EXC Function

The QUARTILE.EXC function is similar to QUARTILE.INC but calculates quartiles based on a percentile range from 0 to 1, exclusive. This means it interpolates between values, excluding the minimum and maximum from the calculation of quartiles. It's often used when you want a different definition of quartiles, particularly with smaller datasets.

Syntax:

=QUARTILE.EXC(array, quart)

  • array: The range of cells containing your numeric data.
  • quart: The quartile you want to find (1, 2, or 3). Note: 0 and 4 are not valid for this function.

Example:

Using the same data in A1:A10: 10, 12, 15, 18, 20, 22, 25, 28, 30, 32

  1. To find Q1: Enter =QUARTILE.EXC(A1:A10, 1).
  2. To find Q3: Enter =QUARTILE.EXC(A1:A10, 3).
  3. To calculate IQR: Subtract Q1 from Q3.

The results might slightly differ from QUARTILE.INC, especially for smaller datasets.

Method 3: Using PERCENTILE.INC and PERCENTILE.EXC Functions

For more general percentile calculations, or if you prefer to think in terms of percentages, Excel offers the PERCENTILE.INC and PERCENTILE.EXC functions. These are highly versatile as they allow you to specify any percentile (k) between 0 and 1.

Syntax:

  • =PERCENTILE.INC(array, k)
  • =PERCENTILE.EXC(array, k)
  • array: The range of cells containing your numeric data.
  • k: The percentile value between 0 and 1 (e.g., 0.25 for Q1, 0.75 for Q3).

Example:

Using the data in A1:A10: 10, 12, 15, 18, 20, 22, 25, 28, 30, 32

  1. To find Q1:
    • Using inclusive method: =PERCENTILE.INC(A1:A10, 0.25)
    • Using exclusive method: =PERCENTILE.EXC(A1:A10, 0.25)
  2. To find Q3:
    • Using inclusive method: =PERCENTILE.INC(A1:A10, 0.75)
    • Using exclusive method: =PERCENTILE.EXC(A1:A10, 0.75)
  3. To calculate IQR: Subtract Q1 from Q3.

These functions provide the same results as their QUARTILE counterparts when k is 0.25, 0.5, or 0.75.

Method 4: Manual Calculation (for understanding)

While Excel functions are efficient, understanding the manual steps helps grasp the concept of IQR.

  1. Sort Your Data: Arrange all your numbers in ascending order.
  2. Find the Median (Q2): This is the middle value of the entire dataset. If there's an even number of data points, it's the average of the two middle numbers.
  3. Find Q1: This is the median of the lower half of the data (all values below Q2).
    • Note: If your original dataset has an odd number of points, some methods include the median in both halves for Q1/Q3 calculation, while others exclude it. Excel's QUARTILE.INC typically includes it, while QUARTILE.EXC excludes it.
  4. Find Q3: This is the median of the upper half of the data (all values above Q2).
  5. Calculate IQR: Subtract Q1 from Q3 (IQR = Q3 - Q1).

Manual Example:

Data: 10, 12, 15, 18, 20, 22, 25, 28, 30, 32 (already sorted)

  1. Sorted Data: 10, 12, 15, 18, 20, 22, 25, 28, 30, 32 (N=10)
  2. Median (Q2): (20 + 22) / 2 = 21
  3. Lower Half: 10, 12, 15, 18, 20 (N=5)
    • Q1 (Median of Lower Half): 15
  4. Upper Half: 22, 25, 28, 30, 32 (N=5)
    • Q3 (Median of Upper Half): 28
  5. IQR: 28 - 15 = 13

Note: The manual calculation method for Q1 and Q3 can vary slightly depending on whether the median is included or excluded when splitting the data. Excel's functions provide a consistent, programmatic approach.

When to Use Which Excel Function?

  • QUARTILE.INC / PERCENTILE.INC: These are generally recommended for consistency with traditional statistical methods. They are inclusive of the 0th and 100th percentiles.
  • QUARTILE.EXC / PERCENTILE.EXC: These are more suited for specific statistical analyses where you want to exclude the minimum and maximum values from the percentile calculation. They require at least 2 data points for PERCENTILE.EXC and at least 4 for QUARTILE.EXC.

Why is IQR Useful?

  • Outlier Detection: A common rule for identifying outliers is any data point that falls below Q1 - 1.5 * IQR or above Q3 + 1.5 * IQR.
  • Robustness: Unlike the standard deviation or range, IQR is not affected by extreme outliers, making it a more robust measure of spread for skewed data.
  • Data Distribution: It gives a quick sense of how spread out the central portion of your data is.

Conclusion

Calculating the Interquartile Range in Excel is a straightforward process using the built-in QUARTILE.INC, QUARTILE.EXC, PERCENTILE.INC, or PERCENTILE.EXC functions. Understanding how to use these functions empowers you to quickly analyze the spread and identify potential outliers in your datasets, leading to more robust statistical insights.