How to Find the Interquartile Range in Excel

Have you ever looked at a set of data and thought, “Hmm, I wonder how spread out the middle 50% of values are.” To be honest, most people probably haven’t. But, being able to find that range – known as the interquartile range – in Excel is useful for many different types of scenarios.

For example, let’s say you’re looking to move to a new city and want to scope out the housing prices. You can create a spreadsheet in Excel with a wide range of prices in the area, and then find the interquartile range (IQR) to get a pretty accurate idea of how much you should expect to pay for a home in your new city. 

Before we get into how to find the IQR in Excel, let’s put it into perspective by taking a quick trip back to math class when we learned how to calculate it manually.

Finding the Interquartile Range in Small Datasets 

The middle 50% of a dataset’s spread can be measured using the interquartile range (or IQR for short). It’s calculated by subtracting a dataset’s first quartile (Q1) from its third quartile (Q3). This calculation can be performed by including the median value (inclusive) or by excluding the median (exclusive). Don’t worry, Excel provides both options so for now, let’s just understand the basic idea.

The formula for finding the IQR is:

IQR = Q3 – Q1

Exclusive Method

Quartiles are values that divide a dataset into four equal parts (i.e. quarters). For example, let’s say you have a set of numbers that looks like this:

[12, 17, 19, 23, 24, 27, 30, 33, 39, 41, 43]

Find the median value, the number in the middle of the list, and then visually group the numbers above and below it as follows:

[12, 17, 19, 23, 24] 27 [30, 33, 39, 41, 43]

Then, find the median number for these two sets:

[12, 17, 19, 23, 24] 27 [30, 33, 39, 41, 43]

• Quartile 1 (exclusive) is 19
• Quartile 3 (exclusive) is 39.
• IRQ (exclusive) = 39 – 19 = 20. 

Inclusive Method

If we preferred to include the median value of 27 to compute the quartiles, the math would look more like this:

[12, 17, 19, 23, 24, 27] [27, 30, 33, 39, 41, 43]

• Quartile 1 (inclusive) would be the mean of 19 and 23, or 21.
• Quartile 3 (inclusive) would be the mean of 33 and 39, or 36.
• IQR (inclusive) = 36 – 21 = 15.

Summary

While the entire dataset lets us see the full range of values, the interquartile range shows us how the middle range of values is dispersed (or how “far apart” the numbers are).

Pretty easy to find, right?

Manually calculating the interquartile range is simple to do when you have a small set of numbers. But, what if you have hundreds or thousands of them, or the values are way more complicated than simple, whole numbers?

That’s when creating an interquartile range in Excel can save you the headache of calculating those values yourself!

How to Find the Interquartile Range in Excel

Unfortunately, Excel doesn’t have a built-in feature that calculates the interquartile range (yet!), but the good news is that it’s simple to find using the QUARTILE function (which uses the inclusive method). Or, depending on your version of Excel, you may have the QUARTILE.EXC and QUARTILE.INC functions, which allow you to specify the exclusive or inclusive method accordingly.

The quartile functions require two arguments: the array of values and the desired quartile.

• Array represents the series of data points you’re interested in
• Quart defines the quartile you want to calculate

By using the quartile functions, you can find the IQR no matter how large or complex your dataset is. 

Without further ado, here’s how to calculate the interquartile range in Excel in four steps!

Step 1:  Enter your dataset into Excel. In this example, we’ll use a data range of A3:A13.

Step 2: Find the first quartile. You can use the QUARTILE, QUARTILE.INC, or QUARTILE.EXC functions to do this. All accept the same arguments: the range of values and the desired quartile number, ie, 1:

=QUARTILE(A3:A13,1)

=QUARTILE.INC(A3:A13,1)

=QUARTILE.EXC(A3:A13,1)

Step 3: Find the third quartile by using 3 as the quartile argument:

=QUARTILE(A3:A13,3)

=QUARTILE.INC(A3:A13,3)

=QUARTILE.EXC(A3:A13,3)

Step 4: Calculate the interquartile range in Excel by subtracting Q1 from Q3:

If you want to keep your spreadsheet even more concise, you can use the formula =QUARTILE(A3:A13,3)-QUARTILE(A3:A13,1) instead of storing the intermediate results in separate cells.

Why is Finding the IQR Useful?

Now you know how to find the interquartile range in Excel, but in which other situations would that come in handy?

Finding the IQR is especially useful when you want to exclude outliers from your dataset. For example, let’s say you have a data range of 9-15, and then an extreme outlier of 70. Finding the IQR will exclude the extreme outlying number so it doesn’t inaccurately represent the rest of the dataset. 

To return to the earlier housing example, you’d be able to exclude any unusually expensive (or suspiciously cheap…) homes from your search, giving you a more accurate idea of your potential cost of living.

If you work in fields like statistics, finance, medicine, insurance, or more, you’ll probably find yourself calculating the IQR a lot – being able to do so in Excel makes the job so much faster and easier.

Do you have any other tips for calculating the interquartile range in Excel? Let us know in the comments!

Sample File