How to Calculate IQR in Excel: A Complete Step-by-Step Guide

The interquartile range (IQR) is one of the most reliable measures of statistical spread in a dataset. Unlike range, it ignores extreme outliers and focuses on the middle 50% of your data — making it essential for data cleaning, statistical analysis, and building charts like box plots. Excel doesn't have a single dedicated IQR function, but calculating it is straightforward once you understand how the pieces fit together.

What Is the IQR and Why Does It Matter?

The IQR represents the difference between the 75th percentile (Q3) and the 25th percentile (Q1) of a dataset:

IQR = Q3 − Q1

It tells you how spread out the central bulk of your data is. A small IQR means your data clusters tightly around the median. A large IQR indicates more variability across the middle of the dataset.

Common uses include:

• Identifying outliers (values more than 1.5× the IQR below Q1 or above Q3)
• Summarizing data distribution without being skewed by extreme values
• Supporting box-and-whisker plots in Excel
• Quality control and data validation workflows

The Core Excel Formula for IQR

Since Excel has no IQR() function, you combine two QUARTILE functions:

=QUARTILE(data_range, 3) - QUARTILE(data_range, 1) 

For example, if your data sits in cells A2 through A50:

=QUARTILE(A2:A50, 3) - QUARTILE(A2:A50, 1) 

That's it. Excel calculates Q1 and Q3 internally and returns the difference.

QUARTILE vs. QUARTILE.INC vs. QUARTILE.EXC 📊

This is where things get nuanced — and where different users get different results from the same dataset.

Excel offers three versions of the quartile function:

For most everyday Excel users, QUARTILE.INC is the standard choice. If you're working in a context that follows strict statistical conventions — academic research, certain financial models — QUARTILE.EXC may be expected. The two functions can return slightly different Q1 and Q3 values, which means your IQR will differ depending on which you use.

Always check which method your field or collaborator expects before locking in a formula.

Step-by-Step: Calculating IQR in Excel

Step 1 — Organize your data Place your dataset in a single column or row. Ensure there are no blank cells within the range, as these can affect quartile calculations.

Step 2 — Calculate Q1 In an empty cell, enter:

=QUARTILE.INC(A2:A50, 1) 

Step 3 — Calculate Q3 In another empty cell, enter:

=QUARTILE.INC(A2:A50, 3) 

Step 4 — Subtract to get IQR In a third cell, enter:

=Q3_cell - Q1_cell 

Or combine it into a single formula:

=QUARTILE.INC(A2:A50, 3) - QUARTILE.INC(A2:A50, 1) 

Step 5 — Label your cells Add adjacent labels like "Q1," "Q3," and "IQR" so your spreadsheet stays readable, especially if you're sharing it.

Using IQR to Identify Outliers in Excel

Once you have your IQR, you can flag outliers using Excel's logical functions. The standard rule defines outliers as any value:

• Below: Q1 − (1.5 × IQR)
• Above: Q3 + (1.5 × IQR)

Assume Q1 is in cell D2, Q3 is in D3, and IQR is in D4. To flag outliers in column A, enter this in column B:

=IF(OR(A2 < D2 - 1.5*D4, A2 > D3 + 1.5*D4), "Outlier", "Normal") 

Drag it down to apply across your full dataset. You can also combine this with conditional formatting to visually highlight outlier rows — useful when working with large tables. 🎯

Working with Named Ranges and Tables

If your data is formatted as an Excel Table (Insert → Table), you can use structured references in your IQR formula:

=QUARTILE.INC(Table1[Score], 3) - QUARTILE.INC(Table1[Score], 1) 

This approach updates automatically when rows are added to the table — a significant advantage for ongoing data entry or live dashboards. Named ranges (Formulas → Define Name) offer similar flexibility and make formulas easier to audit.

Factors That Affect Your IQR Results

The IQR you calculate isn't a fixed truth — it shifts based on several variables:

• Sample size: Smaller datasets produce less stable quartile estimates. A dataset of 10 values will behave very differently from one with 500.
• Data distribution: Heavily skewed data or multimodal distributions may make IQR less informative on its own.
• Function choice: As noted, QUARTILE.INC and QUARTILE.EXC use different interpolation methods and can produce different results on the same data.
• Presence of duplicates or ties: Large clusters of identical values can compress or distort quartile positions.
• Excel version: Older versions of Excel (pre-2010) only have QUARTILE. Modern versions support all three variants.

When a Single IQR Formula Isn't Enough

For simple datasets, one IQR calculation is sufficient. But analysis gets more complex when you're working with:

• Multiple groups — you may need IQR calculated per category using helper columns or pivot tables
• Dynamic ranges — datasets that grow over time benefit from table references or OFFSET-based formulas
• Large-scale analysis — users running statistical modeling at scale sometimes move to tools like R or Python, where IQR functions are built in and more configurable

The right approach depends on how your data is structured, how often it changes, and what decisions you're making with the results. A one-off calculation for a school project looks nothing like an automated IQR check embedded in a monthly financial report — and Excel gives you enough flexibility to handle both, but the formula setup differs considerably between them.