Calculate the Interquartile Range (IQR) in Excel
You want to calculate the interquartile range (IQR) in Excel, but there's no IQR function.
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
There's also a legacy
QUARTILE function, but it's just an alias for
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
Here's how to do it:
- Q3 in cell D1 is
- Q1 in cell D2 is
- The IQR in cell D3 is
The IQR in this example is 18, based on the exclusive quartile approach.