What IQR is and why you'd calculate it in a spreadsheet

The interquartile range, or IQR, measures the spread of the middle half of your data. It tells you how far apart the 25th and 75th percentiles are — in other words, where the bulk of your values cluster. If you're looking at test scores, salaries, or delivery times, the IQR shows you the range where most of your numbers fall, which is often more useful than looking at the highest and lowest values.

Excel calculates IQR in three steps: find the first quartile (Q1), find the third quartile (Q3), then subtract Q1 from Q3. You do this with the QUARTILE function, which is built into every version of Excel. The whole calculation takes one formula.

You'd use IQR instead of range or standard deviation when you want to ignore outliers — the unusually high or low numbers that skew the picture. A single person earning $500,000 in a dataset of $40,000 salaries will throw off your range and standard deviation, but the IQR stays focused on where the typical salaries actually sit.

Key Takeaways

  • IQR is calculated by subtracting the first quartile from the third quartile using the QUARTILE function in Excel.
  • The formula =QUARTILE(range, 3) - QUARTILE(range, 1) gives you the IQR in a single cell.
  • Excel's QUARTILE function works with any contiguous range of numbers, whether they're in a column or a row.
  • IQR is most useful when your data contains outliers or when you want to focus on where the middle 50 percent of your values fall.

Setting up your data in Excel

Before you calculate, make sure your numbers are in a single column or row with no blank cells in the middle. If your data is scattered across multiple columns or has gaps, Excel's QUARTILE function will either skip the gaps or give you an error. The cleanest approach is to put all your values in column A, starting from A1, with no headers mixed in.

If you do have a header row — like "Test Scores" in A1 and the actual numbers starting in A2 — that's fine. You'll just tell Excel to start the range at A2 instead of A1. For example, if your scores run from A2 to A50, your range is A2:A50.

Check that all your entries are actual numbers, not text that looks like numbers. If a cell contains the number 85 stored as text, Excel will either ignore it or treat it differently. You can spot text numbers by noticing they're left-aligned in the cell instead of right-aligned.

Writing the IQR formula

Open a blank cell where you want the result to appear — usually somewhere below or to the right of your data. Type this formula exactly:

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

Replace A2:A50 with your actual data range. The first QUARTILE function with the number 3 finds the 75th percentile (Q3). The second QUARTILE function with the number 1 finds the 25th percentile (Q1). Subtracting Q1 from Q3 gives you the IQR.

Press Enter. Excel calculates both quartiles and shows you the difference in that cell. The result is a single number — the spread of your middle 50 percent.

Understanding what the QUARTILE numbers mean

The QUARTILE function takes two inputs: your data range and a quartile number. The quartile number tells Excel which percentile to find. Here's what each number does:

  • QUARTILE(range, 0) = the minimum value (0th percentile)
  • QUARTILE(range, 1) = the 25th percentile (Q1, the first quartile)
  • QUARTILE(range, 2) = the 50th percentile (the median)
  • QUARTILE(range, 3) = the 75th percentile (Q3, the third quartile)
  • QUARTILE(range, 4) = the maximum value (100th percentile)

For IQR, you only need 1 and 3. If you wanted to see all four quartiles to understand your data better, you could put each QUARTILE formula in its own cell and label them. That gives you a quick picture of how your data breaks down.

Checking your result against your data

Once you have your IQR number, do a quick sanity check. Look at your actual data and think about whether the IQR makes sense. If your numbers range from 10 to 100, an IQR of 50 means the middle half of your data spreads across 50 units. An IQR of 5 means the middle half is tightly clustered. An IQR of 80 would mean your data is very spread out even in the middle.

If your IQR seems way too large or too small compared to what you see in the data, double-check that your range is correct and that all your cells contain actual numbers. A single text value or blank cell in the middle of your range can throw off the calculation.

You can also calculate Q1 and Q3 separately in their own cells to see the actual values, then subtract them by hand to verify Excel's math. Put =QUARTILE(A2:A50,1) in one cell and =QUARTILE(A2:A50,3) in another, then subtract them. You should get the same IQR.

Using IQR to spot outliers

One reason to calculate IQR is to find values that don't belong. A common rule is that any number below Q1 minus 1.5 times the IQR, or above Q3 plus 1.5 times the IQR, is an outlier. You can set this up in Excel with a formula to flag them automatically.

In a new column next to your data, type a formula like =IF(A2<QUARTILE($A$2:$A$50,1)-1.5*($IQR_CELL),"Outlier",""), where $IQR_CELL is the cell containing your IQR result. The dollar signs lock that cell reference so it doesn't change when you copy the formula down. Any value that falls outside the outlier boundaries will show "Outlier" in that row.

This is useful when you're cleaning data or trying to understand whether an extreme value is a real measurement or a data entry mistake. Outliers aren't always wrong — sometimes they're the most interesting part of your data — but flagging them lets you decide what to do with them.

Frequently Asked Questions

What's the difference between IQR and standard deviation?

Standard deviation measures how spread out all your data is from the average. IQR measures only the middle 50 percent and ignores the extremes. If you have a few very large or very small numbers, standard deviation will be pulled toward them, but IQR won't. Use IQR when outliers exist and you want to focus on typical values.

Can I calculate IQR if my data is in multiple columns?

Not with a single formula. You need to either move all your numbers into one column, or use a formula that combines multiple ranges, like =QUARTILE(A1:A50,3)-QUARTILE(A1:A50,1)+QUARTILE(B1:B50,3)-QUARTILE(B1:B50,1). It's cleaner to consolidate your data first.

Does the order of my numbers matter?

No. QUARTILE automatically sorts your data internally to find the percentiles, so whether your numbers are in random order or already sorted doesn't change the result. You don't have to sort them yourself.

What if I have negative numbers in my data?

QUARTILE handles negative numbers the same way it handles positive ones. The IQR will still be the difference between Q3 and Q1, and it will be correct. Negative numbers don't break the calculation.

Is there a faster way to calculate IQR in newer versions of Excel?

Excel 2010 and later have QUARTILE.INC and QUARTILE.EXC, which are more precise versions of QUARTILE for different statistical situations. For most everyday use, QUARTILE works fine. If you're doing formal statistical analysis, check whether your field uses inclusive or exclusive quartiles and use the matching function.