Interquartile Range in Excel Box Plots Explained

The interquartile range (IQR) in an Excel box plot is the distance between the third quartile (Q3) and the first quartile (Q1): IQR = Q3 − Q1. In the chart, it is the height or length of the box itself; to display its numeric value, calculate Q3 minus Q1 in worksheet cells, because the box plot does not normally print the IQR as a label.

What the interquartile range tells you

Quartiles divide ordered data into four roughly equal parts. Q1 marks the 25th percentile, the point below which about one quarter of observations fall. Q3 marks the 75th percentile, with about three quarters of observations at or below it. The IQR measures the spread of the central half of the data, from Q1 to Q3.

For example, if a group of test scores has Q1 of 62 and Q3 of 80, its IQR is 18 points. That does not mean every score lies between 62 and 80; it means that interval contains the middle half of the ordered observations under the chosen quartile method. The remaining observations are split between the lower and upper quarters.

The IQR is useful when data are skewed or contain unusually high or low values. Unlike the range, which uses only the smallest and largest observations, the IQR is not usually changed much by a single extreme value. It describes spread, not the average or the overall shape of a distribution, so it is best read alongside the median and the rest of the plot.

Where to find the IQR in an Excel box plot

In Excel for Microsoft 365 on Windows, select a suitable numeric data range, then choose Insert > Insert Statistic Chart > Box and Whisker. Excel draws a box for each series. The lower edge represents Q1 and the upper edge represents Q3; the box spans the IQR. If the chart is horizontal, read the same elements from left to right rather than bottom to top.

The line inside the box marks the median, or Q2. Whiskers extend beyond the box, and separate points may appear outside them. Those parts provide context, but they are not part of the IQR: the IQR is only the distance from one box edge to the other. Excel does not necessarily show the numeric Q1, Q3, or IQR beside the chart, so use worksheet calculations when exact values matter.

For a clear chart, put each group in its own column and use a header at the top of each column, such as Control and Treatment. Select the headers and observations together before inserting the chart. If Excel treats rows as groups when you intended columns, select the chart and use Chart Design > Switch Row/Column. On Mac, the ribbon layout and chart insertion labels can differ; look on the Insert tab for the statistical chart option. The instructions here describe Excel for Microsoft 365 on Windows.

Calculate Q1, Q3, and IQR in worksheet cells

For a transparent calculation, first decide which quartile convention you want. Suppose the eight numeric observations are in A2:A9: 4, 7, 8, 10, 12, 13, 15, and 18. Under the inclusive convention, Q1 is 7.75 and Q3 is 13.5, so the IQR is 5.75. Excel can calculate those values directly.

With the observations in A2:A9, use this formula to calculate Q1 with Excel’s inclusive quartile method: =QUARTILE.INC(A2:A9,1). It returns 7.75 for the example data. With the same observations in A2:A9, use this formula to calculate Q3: =QUARTILE.INC(A2:A9,3). It returns 13.5.

With Q1 in C2 and Q3 in C3, calculate the IQR in another cell with =C3-C2. The result is 5.75. You can also calculate the difference directly from the original range. With the observations still in A2:A9, enter =QUARTILE.INC(A2:A9,3)-QUARTILE.INC(A2:A9,1); it returns 5.75. Keeping Q1, Q3, and IQR in separate cells is often easier to audit and explain than using one combined expression.

These functions ignore blank cells and text entries in a referenced range, but numeric zeros are valid observations and affect the quartiles. Check that a blank is genuinely missing data rather than a zero that should be included. If the range contains an error value, the calculation may return an error as well.

Choose the chart’s quartile calculation method

Different quartile conventions can produce different Q1 and Q3 values, especially in small samples. Excel’s box-and-whisker chart lets you choose how to calculate quartiles. Right-click a data series and choose Format Data Series. In the Format Data Series pane, under Series Options, look for Quartile Calculation, then choose Inclusive median or Exclusive median.

With an odd number of observations, the inclusive approach includes the median when finding the quartiles of the lower and upper halves. The exclusive approach leaves the median out of those halves. For the eight values in the example, there is no single middle observation, but the methods can still differ because they use different percentile positions. The inclusive results above are Q1 = 7.75 and Q3 = 13.5. Using QUARTILE.EXC on the same range gives Q1 = 7.25 and Q3 = 14.5, for an IQR of 7.25.

For a consistent comparison, use the same convention for the chart and your worksheet calculations. If the chart is set to inclusive median, calculate its quartiles with QUARTILE.INC; if it is set to exclusive median, use QUARTILE.EXC. This avoids a common puzzle in which the box edge appears not to match a number calculated separately. The specific method matters less than applying it consistently and describing it when reporting results.

How whiskers and outlier points relate to the IQR

In a conventional box plot, the IQR also provides a rule for identifying potential outliers. The lower fence is Q1 − 1.5 × IQR, and the upper fence is Q3 + 1.5 × IQR. A value below the lower fence or above the upper fence is shown as a potential outlier. The whiskers generally reach to the most extreme observed values that remain within those fences; they do not necessarily reach the dataset’s absolute minimum and maximum.

For the inclusive example, Q1 is 7.75, Q3 is 13.5, and the IQR is 5.75. The lower fence is −0.875 and the upper fence is 22.125. All eight observations fall within those fences, so none would be classified as an outlier by this rule. A chart’s whiskers and points should be interpreted as a display of the data under its selected settings, not as a verdict that an observation is wrong.

Excel’s Format Data Series options can show or hide outlier points, inner points, mean markers, and a mean line. These options change what is visible, not the definition of the IQR. The mean is also distinct from the median: an extreme observation can pull the mean more strongly, while the median and IQR are based on positions in the ordered data.

Read the box plot without confusing spread measures

A box plot presents several summaries at once. The box shows the central half of the data; the median line shows its midpoint; and the whiskers and any plotted points show values outside the box. A narrow box indicates a relatively small IQR compared with another group measured on the same scale. It does not prove that every part of that group’s distribution is less variable, because the whiskers and outliers may differ.

The IQR is not the full range, standard deviation, or variance. The range is maximum minus minimum. Standard deviation describes typical distance from the mean using a different calculation, and variance is the squared version of that measure. These quantities answer related but different questions. For skewed data or data with extreme values, comparing medians and IQRs can be more informative than comparing means and standard deviations alone.

When comparing groups, make sure they use the same measurement units and the same quartile convention. Also consider sample size: a box plot can summarize a group with few observations, but its quartiles may be based on very little information. Showing individual data points, when appropriate, can help readers see whether a box represents many observations or only a small sample.

Common Excel box plot problems

The chart has one box instead of one per group: Check whether each group is in a separate column or row and whether Excel interpreted the headers correctly. Use Chart Design > Switch Row/Column to change the series orientation.
The IQR formula does not match the box: Check the chart’s Quartile Calculation setting and make the worksheet use the matching inclusive or exclusive function. Confirm that the selected cell range contains the same observations as the chart.
Zeros or unusual values shift the box: Verify that zeros are real observations and not placeholders for missing data. Look for values stored as numbers, accidental duplicates, or extra records included in the selected range.
The whiskers do not reach the minimum and maximum: That can be expected when values beyond the 1.5-IQR fences are displayed separately as outliers. Inspect the plotted points and the series options before assuming the chart omitted data.
Quartile results differ between reports: Ask which quartile method was used. Different software or conventions may interpolate percentiles differently, particularly with small datasets; a difference does not automatically mean the arithmetic is wrong.

Frequently Asked Questions

What is the formula for IQR in Excel?
Subtract Q1 from Q3. With observations in A2:A9, use =QUARTILE.INC(A2:A9,3)-QUARTILE.INC(A2:A9,1) for the inclusive method. Choose the exclusive method instead when that is how the chart or analysis is defined.

Is the box in an Excel box plot the IQR?
Yes. Its two edges mark Q1 and Q3, so the box spans the IQR. The median is the line inside the box; whiskers and outlier points are separate parts of the plot.

Does Excel show the IQR number on the chart?
Not as a standard automatic box-plot label. Calculate Q1 and Q3 in worksheet cells and subtract them, then place the result in a nearby cell or add a manually prepared chart annotation if readers need the exact number.

Why is my Excel IQR different from another calculator’s result?
The tools may use different quartile or percentile conventions. Check whether each uses an inclusive or exclusive calculation, whether the same values were included, and whether blanks or zeros were treated consistently.

Does a point beyond a whisker mean the data is an error?
No. It is a potential outlier under the chart’s rule, not proof of a mistake. Check the source and context before deciding whether the value is valid, a recording error, or an unusual but meaningful observation.

When to report the IQR

Report the IQR when readers need a compact measure of the spread of the middle half of a dataset, especially when the distribution is skewed or contains extreme observations. Pair it with the median and state the quartile convention if exact reproducibility matters. In Excel, the chart communicates that spread visually, while a formula in the worksheet supplies the precise value and makes the calculation easy to verify.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.