Eric Campbell 6 min read Statistics

How to Calculate the Interquartile Range (IQR) in Excel

The Interquartile Range (IQR) is one of the most practical measures of statistical dispersion you'll use in data analysis. It represents the range of the middle 50% of your data—calculated by...

Key Insights

  • The Interquartile Range (IQR) measures the spread of the middle 50% of your data by subtracting Q1 from Q3, making it resistant to outliers unlike standard deviation
  • Excel offers two quartile functions—QUARTILE.INC (includes endpoints) and QUARTILE.EXC (excludes endpoints)—and choosing the wrong one can significantly skew your analysis
  • Combining IQR with the 1.5×IQR rule gives you a statistically sound method for detecting outliers that’s more robust than arbitrary threshold picking

Introduction to IQR

The Interquartile Range (IQR) is one of the most practical measures of statistical dispersion you’ll use in data analysis. It represents the range of the middle 50% of your data—calculated by subtracting the first quartile (Q1, the 25th percentile) from the third quartile (Q3, the 75th percentile).

Why does this matter? Unlike range or standard deviation, IQR isn’t thrown off by extreme values. When you’re analyzing salary data with a few executives making 10x the median, or website response times with occasional timeout spikes, IQR gives you a reliable picture of typical variation.

Throughout this article, we’ll work with a sample dataset of monthly sales figures:

| Row | A (Sales) |
|-----|-----------|
| 1   | Sales     |
| 2   | 12500     |
| 3   | 15200     |
| 4   | 14800     |
| 5   | 18900     |
| 6   | 13400     |
| 7   | 16700     |
| 8   | 45000     |
| 9   | 14200     |
| 10  | 15800     |
| 11  | 17300     |
| 12  | 13900     |
| 13  | 16100     |

Notice row 8 contains 45000—an obvious outlier we’ll identify later using IQR-based detection.

Method 1: Using QUARTILE.INC Function

The QUARTILE.INC function is Excel’s standard approach for calculating quartiles. It uses an inclusive method, meaning it includes the minimum and maximum values (0th and 4th quartiles) in its interpolation calculations.

Here’s the step-by-step approach:

# Cell C2 - Calculate Q1 (First Quartile)
=QUARTILE.INC(A2:A13, 1)
# Result: 14050

# Cell C3 - Calculate Q3 (Third Quartile)
=QUARTILE.INC(A2:A13, 3)
# Result: 17150

# Cell C4 - Calculate IQR
=C3-C2
# Result: 3100

The second argument in QUARTILE.INC specifies which quartile to return:

  • 0 = Minimum value
  • 1 = First quartile (25th percentile)
  • 2 = Median (50th percentile)
  • 3 = Third quartile (75th percentile)
  • 4 = Maximum value

This method works well when you need to see each component separately, which is useful for reporting or when you’ll reuse Q1 and Q3 values in other calculations.

Method 2: Using QUARTILE.EXC Function

QUARTILE.EXC uses an exclusive method that excludes the endpoints (0th and 4th quartiles) from its calculations. This aligns with how some statistical software packages and academic contexts define quartiles.

# Using QUARTILE.EXC
=QUARTILE.EXC(A2:A13, 1)
# Result: 13900

=QUARTILE.EXC(A2:A13, 3)
# Result: 17300

# IQR with EXC method
=QUARTILE.EXC(A2:A13, 3) - QUARTILE.EXC(A2:A13, 1)
# Result: 3400

Here’s a side-by-side comparison:

| Measure | QUARTILE.INC | QUARTILE.EXC |
|---------|--------------|--------------|
| Q1      | 14050        | 13900        |
| Q3      | 17150        | 17300        |
| IQR     | 3100         | 3400         |

When should you use each? Use QUARTILE.INC when you need compatibility with Excel’s PERCENTILE.INC function or when following conventions that include endpoints. Use QUARTILE.EXC when your statistical methodology specifically calls for exclusive quartiles, or when matching results from R’s default quantile function or Python’s numpy with interpolation=‘exclusive’.

My recommendation: stick with QUARTILE.INC unless you have a specific reason to use EXC. It’s more widely understood and produces results that match most online calculators and introductory statistics courses.

Method 3: Single-Cell IQR Formula

When you just need the IQR value without displaying intermediate calculations, combine everything into one formula:

# Direct IQR calculation in a single cell
=QUARTILE.INC(A2:A13, 3) - QUARTILE.INC(A2:A13, 1)
# Result: 3100

For cleaner, more maintainable spreadsheets, define a named range for your data. Select A2:A13, then go to Formulas → Define Name and call it “SalesData”. Now your formula becomes:

# Using named range for clarity
=QUARTILE.INC(SalesData, 3) - QUARTILE.INC(SalesData, 1)

This approach has several advantages. Your formulas become self-documenting—anyone reading the spreadsheet immediately understands what “SalesData” refers to. If your data range expands, you update the named range once rather than hunting through multiple formulas. It also reduces errors from inconsistent range references.

For dynamic ranges that automatically expand with new data, use a table (Ctrl+T) or create a dynamic named range:

# Dynamic named range formula (in Name Manager)
=OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A:$A)-1, 1)

Using IQR to Detect Outliers

The 1.5×IQR rule is the standard method for identifying outliers. Any value below Q1 - 1.5×IQR or above Q3 + 1.5×IQR is flagged as a potential outlier.

# Cell E2 - Lower Bound
=QUARTILE.INC(SalesData, 1) - 1.5 * (QUARTILE.INC(SalesData, 3) - QUARTILE.INC(SalesData, 1))
# Result: 9400

# Cell E3 - Upper Bound
=QUARTILE.INC(SalesData, 3) + 1.5 * (QUARTILE.INC(SalesData, 3) - QUARTILE.INC(SalesData, 1))
# Result: 21800

# Cell B2 (copied down) - Flag outliers
=IF(OR(A2<$E$2, A2>$E$3), "Outlier", "Normal")

With our sample data, this formula correctly identifies 45000 as an outlier (it exceeds the upper bound of 21800).

For visual identification, apply conditional formatting:

  1. Select your data range (A2:A13)
  2. Go to Home → Conditional Formatting → New Rule
  3. Select “Use a formula to determine which cells to format”
  4. Enter the formula:
=OR(A2<$E$2, A2>$E$3)
  1. Set the format (red fill, bold text, etc.)
  2. Click OK

Now outliers automatically highlight in red, making them immediately visible when scanning your data.

For a more sophisticated approach, create an outlier classification column:

# Cell B2 - Detailed outlier classification
=IF(A2<$E$2, "Low Outlier", IF(A2>$E$3, "High Outlier", "Within Range"))

Visualizing IQR with Box Plots

Excel’s box-and-whisker chart displays quartiles visually, making it easy to spot the IQR, median, and outliers at a glance.

Here’s how to create one:

  1. Select your data range including the header (A1:A13)
  2. Go to Insert → Charts → Statistical Charts (the icon with a histogram)
  3. Select “Box and Whisker”

Excel automatically calculates and displays:

  • The box spanning from Q1 to Q3 (the IQR)
  • A line inside the box showing the median
  • Whiskers extending to the minimum and maximum non-outlier values
  • Individual points for outliers

To customize the chart for better clarity:

Right-click the chart → Format Data Series:
- Set "Show inner points" to display all data points
- Set "Show outlier points" to highlight outliers
- Set "Show mean markers" to add a mean indicator
- Adjust gap width to control box thickness

For multiple datasets, arrange your data in columns:

| A (Region 1) | B (Region 2) | C (Region 3) |
|--------------|--------------|--------------|
| 12500        | 11200        | 14500        |
| 15200        | 13800        | 16200        |
| ...          | ...          | ...          |

Select all columns and create the chart. Excel generates side-by-side box plots, perfect for comparing distributions across categories.

Common Errors and Troubleshooting

#NUM! Error

This occurs when your data range has fewer than 4 values (you need at least 4 data points to calculate quartiles meaningfully) or when you pass an invalid quartile number:

# This causes #NUM! error
=QUARTILE.INC(A2:A4, 1)  # Only 3 data points

# This also causes #NUM! error
=QUARTILE.INC(A2:A13, 5)  # Invalid quartile (must be 0-4)

Text Values in Data Range

QUARTILE functions ignore text values, which can produce unexpected results:

# If A5 contains "N/A" instead of a number
=QUARTILE.INC(A2:A13, 1)  # Calculates using only 11 values

Clean your data first, or use helper columns with IFERROR to handle missing values explicitly.

QUARTILE vs QUARTILE.INC

The old QUARTILE function still works but is deprecated. Microsoft recommends QUARTILE.INC for new spreadsheets:

# Deprecated (still works, but avoid in new work)
=QUARTILE(A2:A13, 1)

# Recommended
=QUARTILE.INC(A2:A13, 1)

Inconsistent Results with Other Tools

If your Excel IQR doesn’t match results from R, Python, or other software, check which interpolation method they use. Different tools default to different methods. QUARTILE.INC corresponds to “type 7” interpolation in R’s quantile function.

Empty Cells vs Zero Values

Empty cells are ignored; zero values are included. This matters significantly:

# Range with empty cell: calculates quartile of non-empty values
# Range with zero: includes zero in calculation, potentially skewing results

When preparing data, decide deliberately whether missing values should be blank or zero—they’re not interchangeable for statistical calculations.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.

Similar Articles