The quickest way to calculate IQR in Excel

The interquartile range (IQR) is the distance between the 25th percentile and the 75th percentile of your data — it tells you where the middle half of your values fall. In Excel, you calculate it by finding the third quartile (Q3) and first quartile (Q1), then subtracting: IQR = Q3 − Q1.

Excel has a QUARTILE function that does this work for you. The formula is straightforward: =QUARTILE(data range, 3) − QUARTILE(data range, 1). If your data sits in cells A2 through A50, you would write =QUARTILE(A2:A50,3)−QUARTILE(A2:A50,1) in an empty cell and press Enter.

Key Takeaways

  • IQR measures the spread of the middle 50 percent of your data by subtracting the first quartile from the third quartile.
  • Use the QUARTILE function with your data range and the numbers 1 and 3 to find Q1 and Q3.
  • You can calculate IQR in a single formula by nesting two QUARTILE functions with subtraction.
  • Excel also offers QUARTILE.INC and QUARTILE.EXC as alternatives, though QUARTILE works in all versions.
  • The IQR helps you identify outliers and understand whether your data clusters tightly or spreads widely.

Step-by-step: Setting up your IQR calculation

Start by arranging your data in a single column. It does not need to be sorted — Excel handles that internally. Click on an empty cell where you want the result to appear, such as a cell to the right of your data or below it.

Type the formula exactly as shown: =QUARTILE(A2:A50,3)−QUARTILE(A2:A50,1), but replace A2:A50 with the actual range of your data. If your numbers are in column B from row 1 to row 100, write =QUARTILE(B1:B100,3)−QUARTILE(B1:B100,1) instead. Press Enter, and Excel calculates the result in that cell.

Understanding what the numbers mean in QUARTILE

The QUARTILE function takes two pieces of information: your data range and a quartile number. The number tells Excel which quartile to find. The number 1 means the first quartile (25th percentile), and the number 3 means the third quartile (75th percentile). The number 2 would give you the median, and 0 or 4 give you the minimum or maximum.

When you subtract Q1 from Q3, you get the range that contains the middle 50 percent of your values. This is useful for spotting outliers or understanding whether your data is tightly clustered or spread out. A small IQR means most of your data points are close together; a large IQR means they are more scattered.

Using separate cells for Q1 and Q3 (optional)

If you want to see Q1 and Q3 separately before calculating IQR, create them in their own cells first. In one cell, type =QUARTILE(A2:A50,1) and label it Q1. In another cell, type =QUARTILE(A2:A50,3) and label it Q3. Then in a third cell, subtract the first from the second: =Q3_cell−Q1_cell.

This approach makes it easier to check your work and understand what is happening at each step. It also lets you reuse Q1 and Q3 in other calculations if you need them later. For example, you might use Q1 and Q3 to identify outliers by finding values that fall below Q1 minus 1.5 times the IQR, or above Q3 plus 1.5 times the IQR.

QUARTILE.INC versus QUARTILE.EXC

Excel offers two versions of the quartile function: QUARTILE.INC (inclusive) and QUARTILE.EXC (exclusive). The older QUARTILE function is the same as QUARTILE.INC. The difference is in how they handle the edges of your data when calculating percentiles — QUARTILE.INC includes the minimum and maximum values in its calculation, while QUARTILE.EXC excludes them.

For most everyday use, QUARTILE or QUARTILE.INC gives results that match what most statistics textbooks expect. If you are working with a sample of data and want to exclude the extremes, use QUARTILE.EXC instead. The formula works the same way: =QUARTILE.EXC(A2:A50,3)−QUARTILE.EXC(A2:A50,1). The results will differ slightly, but both are mathematically valid depending on your purpose.

Common mistakes to avoid

The most frequent error is forgetting to include both QUARTILE functions in the formula. Writing only =QUARTILE(A2:A50,3) gives you Q3 alone, not the IQR. You must subtract Q1 from it to get the range.

Another mistake is using the wrong quartile numbers. Remember: 1 is the first quartile (Q1), and 3 is the third quartile (Q3). Using 2 and 4, or 0 and 3, will give you the wrong answer. Also check that your data range is correct — if you accidentally include a header row with text, Excel will ignore it, but if you include an empty row in the middle of your data, it may cause unexpected results.

Frequently Asked Questions

What if my data has empty cells or text values?

QUARTILE ignores empty cells and text automatically, so they do not affect the calculation. If a cell contains text that looks like a number, Excel treats it as text and skips it. Make sure your actual numeric data is in cells formatted as numbers, not text.

Can I calculate IQR for multiple columns at once?

Yes. Create the formula for the first column, then copy the cell and paste it into cells next to the other columns. Excel adjusts the cell references automatically. For example, if your formula in C2 is =QUARTILE(A2:A50,3)−QUARTILE(A2:A50,1), pasting it into D2 changes it to reference column B instead.

Why are my Q1 and Q3 values not whole numbers?

QUARTILE calculates percentiles by interpolating between values in your data, so Q1 and Q3 are often decimals even if all your data points are whole numbers. This is correct — the quartiles represent positions between your actual data points, not necessarily values that appear in your dataset.

Does the order of my data matter?

No. QUARTILE sorts your data internally, so whether your numbers are in ascending order, descending order, or random order, the result is the same. You do not need to sort before calculating.