Calculate the Interquartile Range (IQR) in Excel

Your goal

You want to calculate the interquartile range (IQR) in Excel, but there's no IQR function.

Step-by-step tutorial

You're right—Excel doesn't have an IQR function. That means you'll have to get your hands dirty (but only slightly) and work directly with Excel's quartile functions.

Step 1: Put your dataset in a column

For the purposes of this tutorial, we'll use the following small dataset:

83, 87, 61, 92, 38, 78, 73, 55, 98, 74, 86, 69, 40, 83

Step 2: Calculate the IQR from the first and third quartiles

In Excel, we have to calculate the IQR manually from quartiles. This is easy to do, as the IQR is just Q3 - Q1. Excel has two quartile functions: QUARTILE.EXC (exclusive) and QUARTILE.INC (inclusive).

There's also a legacy QUARTILE function, but it's just an alias for QUARTILE.INC.

It doesn't matter much which quartile function you use, so we'll use QUARTILE.EXC since that's what Excel box and whisker plots use behind the scenes.

Here's how to do it:

How to calculate the IQR from quartiles
How to calculate the IQR from quartiles

where

  • Q3 in cell D1 is =QUARTILE.EXC(A1:A14,3)
  • Q1 in cell D2 is =QUARTILE.EXC(A1:A14,1)
  • The IQR in cell D3 is =D2-D1

For example:

How to calculate Q1 using QUARTILE.EXC
How to calculate Q1 using QUARTILE.EXC

The IQR in this example is 18, based on the exclusive quartile approach.