What the IQR tells you and why Excel makes it straightforward

The interquartile range (IQR) is the spread of the middle half of your data — the distance between the 25th percentile and the 75th percentile. If you have test scores, sales figures, or any list of numbers, the IQR shows you where most of your data clusters, ignoring the extreme highs and lows. Excel calculates it in two steps using the QUARTILE function, and the whole process takes less than a minute once you know the syntax.

You need the IQR when you want to spot outliers, understand whether your data is tightly grouped or spread out, or compare the consistency of two datasets. A narrow IQR means your numbers are similar to each other. A wide IQR means they vary a lot. Excel does the math; you just need to point it at the right cells and use the right formula.

Key Takeaways

  • The IQR is calculated by subtracting the first quartile (25th percentile) from the third quartile (75th percentile) using the QUARTILE function.
  • In Excel, the formula is =QUARTILE(range, 3) - QUARTILE(range, 1), where range is your data column or row.
  • You can calculate the quartiles in separate cells first, then subtract them, or write the entire formula in one cell.
  • The IQR helps you identify outliers: any value below Q1 minus 1.5×IQR or above Q3 plus 1.5×IQR is considered an outlier.

Setting up your data in Excel

Start by entering your numbers in a single column or row. For example, if you have 20 test scores, put them in cells A1 through A20. Excel does not care whether your data is vertical or horizontal, but keeping it in one continuous block makes the formula easier to write and less error-prone.

Make sure your data contains only numbers. If a cell holds text, a blank space, or a formula error, Excel will either skip it or return an error depending on the function. If you have headers (like "Test Score" in A1), start your data in A2 instead and adjust your formula range accordingly.

The two-step method: calculating Q1 and Q3 separately

The clearest way to calculate IQR is to find the first and third quartiles in separate cells, then subtract them. This method lets you see both numbers and makes it straightforward to spot mistakes.

In an empty cell, type =QUARTILE(A1:A20, 1) to find the 25th percentile (Q1). The 1 at the end tells Excel which quartile you want. In another empty cell below it, type =QUARTILE(A1:A20, 3) to find the 75th percentile (Q3). The 3 means the third quartile.

Once you have both numbers, subtract Q1 from Q3 in a third cell. If Q1 is in cell C1 and Q3 is in cell C2, type =C2-C1. That result is your IQR. This approach is slower than a single formula, but it is easier to check your work and explain to someone else what you did.

The one-formula method: calculating IQR directly

If you want the answer in one cell, use this formula: =QUARTILE(A1:A20, 3) - QUARTILE(A1:A20, 1). Replace A1:A20 with your actual data range. Excel calculates both quartiles and subtracts them in a single step.

This method is faster and takes up less space on your sheet. The downside is that you cannot see the individual quartile values unless you hover over the formula bar. If you need to report Q1 and Q3 separately for a presentation or analysis, use the two-step method instead.

Using QUARTILE.INC and QUARTILE.EXC: which one to choose

Excel offers two versions of the quartile function: QUARTILE.INC and QUARTILE.EXC. The older QUARTILE function is the same as QUARTILE.INC. Both work the same way — you give them a range and a quartile number — but they calculate slightly different values because they use different methods to estimate where the quartiles fall.

For most purposes, QUARTILE.INC (the default) is fine. It includes the minimum and maximum values in its calculation. QUARTILE.EXC excludes them. The difference is usually small unless your dataset is very small (fewer than 10 values). If your textbook or workplace specifies which one to use, follow that instruction. Otherwise, stick with QUARTILE or QUARTILE.INC.

Finding outliers using the IQR

Once you have the IQR, you can use it to flag unusual values. An outlier is any number that falls below Q1 minus 1.5 times the IQR, or above Q3 plus 1.5 times the IQR. This rule is called the 1.5×IQR method and is standard in statistics.

If your Q1 is 50, your Q3 is 80, and your IQR is 30, then the lower boundary is 50 − (1.5 × 30) = 5, and the upper boundary is 80 + (1.5 × 30) = 125. Any value below 5 or above 125 is an outlier. You can write a formula to flag these automatically: =IF(A1 < $C$1 - 1.5*$C$3, "Outlier", "Normal"), where C1 holds Q1 and C3 holds the IQR. Use dollar signs to lock the cell references so the formula does not change when you copy it down.

Common mistakes and how to fix them

The most common error is forgetting to change the range. If you copy a formula from one dataset to another, make sure the cell references (A1:A20, for example) match your new data. A second mistake is including text or blank cells in your range. Excel usually handles this gracefully, but if you get an error, check that every cell in your range contains a number.

Another trap is using the wrong quartile number. Remember: 1 means the first quartile (25th percentile), and 3 means the third quartile (75th percentile). If you type 2, you get the median, which is not what you want for IQR. If you type 0 or 4, you get the minimum or maximum, which are also not quartiles.

Frequently Asked Questions

What is the difference between IQR and standard deviation?

The IQR measures the spread of the middle 50 percent of your data and ignores outliers. Standard deviation measures how far, on average, each value is from the mean and is affected by extreme values. Use IQR when you want a straightforward picture of where most of your data sits. Use standard deviation when you want to know how tightly clustered everything is around the average.

Can I calculate IQR for data in multiple columns?

Yes, but you need a separate formula for each column. Write =QUARTILE(A1:A20, 3) - QUARTILE(A1:A20, 1) for column A, then =QUARTILE(B1:B20, 3) - QUARTILE(B1:B20, 1) for column B. If you want to compare the IQR across many columns, calculate each one in its own cell and arrange them in a row so you can see them side by side.

What if my data has negative numbers?

QUARTILE works with negative numbers the same way it works with positive ones. The formula does not change. If your data ranges from −50 to 100, Excel will calculate Q1, Q3, and the IQR correctly. Negative numbers are treated as smaller than positive numbers, so they will fall toward the bottom of the sorted list.

Does the order of my data matter?

No. QUARTILE automatically sorts your data internally before calculating quartiles. You do not need to sort it yourself. Whether your numbers are in random order or already sorted, the result is the same.

How do I know if my IQR is large or small?

Compare it to the range of your data (maximum minus minimum) or to the mean. If the IQR is much smaller than the range, your data has outliers or extreme values. If the IQR is close to the range, your data is fairly evenly spread. There is no universal "good" or "bad" IQR — it depends on what you are measuring and what you expect.