The two ways to find IQR in Excel

Interquartile range (IQR) measures the spread of the middle 50% of your data — the distance between the 25th percentile and the 75th percentile. Excel calculates it in two ways: using the QUARTILE function or the QUARTILE.INC function. Both give you the same result in most cases, though QUARTILE.INC is the newer version and works the same way across all recent Excel versions.

The fastest route is to use QUARTILE.INC twice in separate cells, subtract one from the other, and you have your IQR. If you are working with older Excel files or sharing with someone using an older version, QUARTILE works identically. The choice between them matters only if you are working with very small datasets or need to match a specific statistical method your organization uses.

Key Takeaways

  • IQR is the difference between the 75th percentile (third quartile) and the 25th percentile (first quartile) of your data.
  • Use =QUARTILE.INC(range,3)-QUARTILE.INC(range,1) to calculate IQR in a single formula, where range is your data column or row.
  • QUARTILE and QUARTILE.INC produce identical results; use QUARTILE.INC in new spreadsheets and QUARTILE only if you need compatibility with older Excel versions.
  • IQR helps you spot outliers and understand whether your data clusters tightly or spreads widely across the middle values.

Setting up your data in Excel

Arrange your numbers in a single column or row — Excel does not care which, as long as they are contiguous. For example, put your values in cells A1 through A20, or B5 through B24. If your data has a header row (like "Sales" or "Temperature"), start your range on the first data value, not the header.

Excel ignores empty cells and text within a range, so if you have a few blank rows mixed in, the formula still works. However, if a cell contains text that looks like a number (like "5.2" stored as text instead of a number), Excel may skip it. To check: click a cell with a number. If it aligns left instead of right, it is stored as text. Convert it by copying the column, using Paste Special with the "Add" operation, then deleting the helper column.

Using QUARTILE.INC to find Q1 and Q3

Click on an empty cell where you want to see your first quartile (Q1). Type =QUARTILE.INC(A1:A20,1) and press Enter. The 1 at the end tells Excel you want the 25th percentile. The result is Q1.

In another empty cell, type =QUARTILE.INC(A1:A20,3) and press Enter. The 3 tells Excel you want the 75th percentile. This is Q3. Now you have both numbers. Subtract Q1 from Q3 to get IQR, or use a single formula in one cell: =QUARTILE.INC(A1:A20,3)-QUARTILE.INC(A1:A20,1).

Calculating IQR in one formula

The single-formula approach saves steps and reduces the chance of error. Click an empty cell and type the formula exactly as shown: =QUARTILE.INC(A1:A20,3)-QUARTILE.INC(A1:A20,1). Replace A1:A20 with your actual data range. Press Enter, and Excel displays your IQR.

If your data spans a different range — say C5 through C50 — change both instances of A1:A20 to C5:C50. The formula structure stays the same. You can also name your data range first (go to the Name Box, type a name like "Sales_Data", press Enter, then select your range and press Enter again), then use =QUARTILE.INC(Sales_Data,3)-QUARTILE.INC(Sales_Data,1). Named ranges make long spreadsheets easier to read.

When to use QUARTILE instead of QUARTILE.INC

QUARTILE is the older function name and works identically to QUARTILE.INC in Excel 2010 and later. If you are sharing your file with someone using Excel 2007 or earlier, use QUARTILE instead: =QUARTILE(A1:A20,3)-QUARTILE(A1:A20,1). The result is the same.

In practice, this matters only if you know your audience uses very old versions. Most organizations have upgraded, so QUARTILE.INC is the safer choice for new work. If you inherit a spreadsheet full of QUARTILE formulas, leave them as they are — they work fine and changing them serves no purpose.

What your IQR result tells you

A small IQR means your middle 50% of values cluster tightly together. A large IQR means they spread widely. For example, if test scores have an IQR of 5 points, most students scored within a narrow band. If IQR is 20 points, performance varies much more.

IQR also helps you find outliers. Any value below Q1 minus 1.5 times the IQR, or above Q3 plus 1.5 times the IQR, is often considered an outlier. To find these boundaries, calculate =QUARTILE.INC(A1:A20,1)-(1.5*IQR_cell) for the lower bound and =QUARTILE.INC(A1:A20,3)+(1.5*IQR_cell) for the upper bound, where IQR_cell is the cell containing your IQR result.

Frequently Asked Questions

What is the difference between QUARTILE and QUARTILE.INC?

QUARTILE and QUARTILE.INC produce identical results in modern Excel. QUARTILE.INC is the current standard name; QUARTILE is the older name kept for compatibility. Use QUARTILE.INC in new spreadsheets unless you need to share with someone using Excel 2007 or earlier.

Can I calculate IQR for data in multiple columns?

No, QUARTILE.INC works on one range at a time. If you need IQR for each column separately, create a separate formula for each column. If you need to combine data from multiple columns into one IQR, copy all values into a single column first, then explore the formula.

What does it mean if my IQR is zero?

An IQR of zero means Q1 and Q3 are the same value — your middle 50% of data points are identical. This happens with small datasets or when many values repeat. It is not an error; it accurately describes your data.

How do I use IQR to find outliers in my data?

Calculate the lower fence as Q1 minus 1.5 times IQR, and the upper fence as Q3 plus 1.5 times IQR. Any value outside these fences is typically considered an outlier. Use formulas like =QUARTILE.INC(A1:A20,1)-(1.5*(QUARTILE.INC(A1:A20,3)-QUARTILE.INC(A1:A20,1))) to find the lower fence.

Does the order of my data matter?

No. Excel automatically sorts your data internally to find quartiles, so the order you enter values does not affect the result. You can paste data in any order and get the same IQR.