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

Enter your data points separated by commas. E.g., 1, 5, 8, 12, 15, 19, 22, 25

The Interquartile Range (IQR) is a crucial measure of statistical dispersion, representing the spread of the middle 50% of your data. Unlike the standard range (maximum - minimum), the IQR is robust to outliers, making it a preferred measure for skewed distributions or datasets containing extreme values. Understanding how to calculate it, especially in a powerful tool like Excel, is an essential skill for anyone working with data.

What is the Interquartile Range (IQR)?

The Interquartile Range 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 / Lower Quartile): The value below which 25% of the data falls.
  • Q2 (Second Quartile / Median): The value below which 50% of the data falls.
  • Q3 (Third Quartile / Upper Quartile): The value below which 75% of the data falls.

The IQR therefore captures the central spread of the data, ignoring the lowest 25% and highest 25% of values. This makes it particularly useful for identifying potential outliers (values that fall below Q1 - 1.5 * IQR or above Q3 + 1.5 * IQR).

Manual Calculation of IQR

Before diving into Excel, let's briefly review the manual steps:

  1. Order the Data: Arrange all your data points in ascending order.
  2. Find the Median (Q2): This is the middle value of your dataset. If you have an odd number of data points, it's the exact middle value. If you have an even number, it's the average of the two middle values.
  3. Find Q1: This is the median of the lower half of your data (all values below Q2).
  4. Find Q3: This is the median of the upper half of your data (all values above Q2).
  5. Calculate IQR: Subtract Q1 from Q3 (IQR = Q3 - Q1).

Note: There are slightly different methods for defining quartiles, especially when dealing with smaller datasets or when the median itself is included/excluded from the halves. Excel offers functions that align with common statistical practices, which we'll explore below.

Calculating IQR in Excel

Excel provides dedicated functions to calculate quartiles, making the process straightforward. The most common functions are QUARTILE.INC and QUARTILE.EXC.

1. Using the QUARTILE.INC Function (Inclusive Method)

The QUARTILE.INC function calculates quartiles inclusively, meaning it includes the minimum and maximum values in its calculation of the range for percentiles. This is the more commonly used method and often aligns with how quartiles are taught in introductory statistics.

Syntax:

=QUARTILE.INC(array, quart)

  • array: The range of cells containing your numeric data.
  • quart: The value indicating which quartile you want to find:
    • 0: Minimum value
    • 1: First Quartile (25th percentile)
    • 2: Second Quartile (Median, 50th percentile)
    • 3: Third Quartile (75th percentile)
    • 4: Maximum value

Example:

Let's use the following dataset: 10, 15, 20, 25, 30, 35, 40, 45, 50

Assume this data is in cells A1:A9.

  • To find Q1: =QUARTILE.INC(A1:A9, 1) which would return 20
  • To find Q3: =QUARTILE.INC(A1:A9, 3) which would return 40
  • To find IQR: =QUARTILE.INC(A1:A9, 3) - QUARTILE.INC(A1:A9, 1) which would return 40 - 20 = 20

2. Using the QUARTILE.EXC Function (Exclusive Method)

The QUARTILE.EXC function calculates quartiles exclusively, meaning it excludes the minimum and maximum values from the calculation. This method is often used in more advanced statistical analysis.

Syntax:

=QUARTILE.EXC(array, quart)

  • array: The range of cells containing your numeric data.
  • quart: The value indicating which quartile you want to find:
    • 1: First Quartile (25th percentile)
    • 2: Second Quartile (Median, 50th percentile)
    • 3: Third Quartile (75th percentile)

Important: QUARTILE.EXC does not accept 0 or 4 for the quart argument. If your dataset has fewer than 3 data points, it will return an error.

Example (using the same dataset: 10, 15, 20, 25, 30, 35, 40, 45, 50 in A1:A9):

  • To find Q1: =QUARTILE.EXC(A1:A9, 1) which would return 17.5
  • To find Q3: =QUARTILE.EXC(A1:A9, 3) which would return 42.5
  • To find IQR: =QUARTILE.EXC(A1:A9, 3) - QUARTILE.EXC(A1:A9, 1) which would return 42.5 - 17.5 = 25

Notice the difference in results between .INC and .EXC. Always be mindful of which method is appropriate for your specific analysis or requirements.

3. Using PERCENTILE.INC and PERCENTILE.EXC

These functions are more generalized versions of the quartile functions, allowing you to find any percentile (not just quartiles). Q1 is the 25th percentile (k=0.25), and Q3 is the 75th percentile (k=0.75).

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, inclusive (for .INC) or exclusive (for .EXC).

Example (using the same dataset in A1:A9):

  • Q1 (Inclusive): =PERCENTILE.INC(A1:A9, 0.25) returns 20
  • Q3 (Inclusive): =PERCENTILE.INC(A1:A9, 0.75) returns 40
  • Q1 (Exclusive): =PERCENTILE.EXC(A1:A9, 0.25) returns 17.5
  • Q3 (Exclusive): =PERCENTILE.EXC(A1:A9, 0.75) returns 42.5

These functions yield the same results as their QUARTILE counterparts but offer more flexibility for other percentile calculations.

When to Use IQR?

The Interquartile Range is particularly useful in several scenarios:

  • Detecting Outliers: As mentioned, IQR is fundamental for defining outlier boundaries (Q1 - 1.5*IQR and Q3 + 1.5*IQR).
  • Skewed Distributions: When data is not normally distributed or contains extreme values, the standard deviation can be misleading. IQR provides a more robust measure of spread.
  • Exploratory Data Analysis: It gives a quick understanding of the central spread of a dataset and can be visualized effectively using box plots.
  • Comparing Distributions: You can compare the spread of different datasets using their IQRs.

Conclusion

Calculating the Interquartile Range in Excel is a straightforward process thanks to the QUARTILE.INC and QUARTILE.EXC functions (or their PERCENTILE equivalents). By understanding the differences between the inclusive and exclusive methods, you can accurately assess the central spread of your data and gain deeper insights, especially when dealing with outliers or non-normal distributions. Mastering this statistical tool will undoubtedly enhance your data analysis capabilities in Excel.