Interquartile range (IQR) is a measure of statistical dispersion, which is the spread of a dataset. It represents the range between the first quartile (25th percentile) and the third quartile (75th percentile) of a dataset. Calculating IQR can help you identify outliers, understand the overall distribution and variability of your data. In this article, we will show you how to calculate IQR in Excel step-by-step.

Step 1: Organize Your Data

First, ensure your data is inputted into Excel in a single column or row. For this example, let’s assume you have a column of numbers in cells A1:A20.

Step 2: Calculate the First Quartile (Q1)

Select an empty cell where you want to display the first quartile value, for example, B1. In that cell, input the following formula:

=QUARTILE.INC(A1:A20, 1)

This formula tells Excel to calculate the value corresponding to the 25th percentile (first quartile) of the dataset.

Step 3: Calculate the Third Quartile (Q3)

In another empty cell (e.g., B2), enter the following formula to find the value corresponding to the 75th percentile (third quartile) for your data:

=QUARTILE.INC(A1:A20, 3)

Step 4: Calculate IQR

Now that you have calculated both Q1 and Q3, finding the interquartile range is simple subtraction. In an empty cell (e.g., B3), subtract the first quartile value from the third quartile value using this formula:

=B2 – B1

The result displayed in cell B3 will be your IQR for that dataset.

Step 5: Identify Outliers (Optional)

If you wish to identify outliers within your dataset, you can use the IQR to do so. Remember that an outlier is a value that falls below Q1 – 1.5 * IQR or above Q3 + 1.5 * IQR. To calculate the lower limit for outliers, use the following formula in another empty cell (e.g., B4):

=B1 – 1.5 * (B3)

Similarly, calculate the upper limit for outliers using this formula in an empty cell (e.g., B5):

=B2 + 1.5 * (B3)

Now, you can identify any data points in your dataset (A1:A20) that fall below the lower limit or above the upper limit as potential outliers.

And there you have it! By following these steps, you can easily calculate IQR and identify outliers within your dataset using Excel. This technique can be applied to various datasets and scenarios to help you analyze and better understand your data’s distribution and variability.