Inclusive vs Exclusive Median in Excel Box Plots Explained

In an Excel box-and-whisker chart, Inclusive median includes the data-set median in both halves when the sample has an odd number of values; Exclusive median leaves it out. This changes how Excel calculates the first and third quartiles, and therefore can change the box and outlier limits, but it does not change the median line itself.

What the two settings mean

A box plot summarizes a distribution using five landmarks: the minimum, first quartile (Q1), median, third quartile (Q3), and maximum or whisker endpoints. The box runs from Q1 to Q3, and the line inside it marks the median. Excel’s inclusive and exclusive choices concern how it finds Q1 and Q3 from the observations; they are not choices about whether to display the median line.

First, sort the observations from smallest to largest and find the overall median. If there are an odd number of values, one value sits exactly in the middle. Under the inclusive method, Excel includes that middle value in each half when finding the halves’ medians. Under the exclusive method, it removes the middle value before making the lower and upper halves. If there are an even number of values, the data split evenly into a lower and upper half, so this include-or-exclude distinction does not apply.

These are two legitimate quartile conventions, not a choice between a correct and an incorrect chart. Different books, statistical packages, and workplace standards may use different conventions. The important thing is to know which convention a chart uses and to apply it consistently when comparing groups.

A worked example with five observations

Suppose a group has the sorted values 1, 2, 3, 4, and 5. The median is 3. With the inclusive method, 3 is included in both halves: the lower half is 1, 2, 3 and the upper half is 3, 4, 5. Their medians give Q1 = 2 and Q3 = 4. With the exclusive method, the middle 3 is set aside: the halves are 1, 2 and 4, 5. Their medians give Q1 = 1.5 and Q3 = 4.5.

So the inclusive box extends from 2 to 4, while the exclusive box extends from 1.5 to 4.5. Both charts have a median line at 3. In this small example, the exclusive box is wider because its quartiles are farther apart. That is a consequence of the convention, not evidence that the underlying five observations changed.

A seven-value example makes the rule especially clear. For 1, 2, 3, 4, 5, 6, 7, the median is 4. Inclusive halves are 1, 2, 3, 4 and 4, 5, 6, 7, giving Q1 = 2.5 and Q3 = 5.5. Exclusive halves are 1, 2, 3 and 5, 6, 7, giving Q1 = 2 and Q3 = 6. The middle observation contributes to both inclusive halves, but to neither exclusive half.

What changes on the chart

The quartiles set the box edges, and their difference, Q3 minus Q1, is the interquartile range (IQR). Changing quartile convention can therefore change the apparent box height or width, depending on the chart orientation, and the IQR. The median marker generally remains at the same value because the median of the full data set has not changed.

Excel also uses the IQR when identifying potential outliers. In the usual box-plot rule, values below Q1 minus 1.5 times the IQR or above Q3 plus 1.5 times the IQR are classified as outliers. The whiskers extend to the most extreme observations that are not classified as outliers; they do not necessarily reach the minimum and maximum. As a result, changing quartiles may change the fences and which values appear as outliers or whisker endpoints. It will not necessarily do so: if every observation remains inside both sets of fences, the whiskers can stay the same.

Excel offers display options as well as the quartile calculation option. Showing inner points can reveal observations between the whisker endpoints, while showing outlier points makes flagged observations visible beyond the whiskers. Mean markers and mean lines are separate from the median and quartile convention. A mean marker should not be mistaken for the median line.

How to change the setting in Excel

These steps apply to Excel for Microsoft 365 on Windows. A box-and-whisker chart needs numeric observations, usually arranged in separate columns for the groups you want to compare, with a heading at the top of each column.

Select the data, including the group headings if you have them.
Choose Insert > Insert Statistic Chart > Box and Whisker.
Click a box in the chart to select its data series, then right-click and choose Format Data Series.
In the formatting pane, open Series Options and find Quartile Calculation.
Choose Inclusive median or Exclusive median. Check the chart again to see whether the box edges, whiskers, or outlier points have moved.

Depending on the chart layout, selecting a box once may select a single point rather than the whole series. If the series options are not visible, click the chart series again or use the chart selection controls to select the series, then reopen Format Data Series. The wording or pane layout can differ slightly in other Excel releases. On Mac, the same chart type and series-formatting controls are available in supported desktop versions, though the ribbon and formatting pane may be arranged differently. Excel for the web may not expose every chart-formatting control; use the desktop app if the quartile option is unavailable.

Choosing the method for your purpose

If a course, report template, company standard, or analysis protocol specifies a quartile convention, use that one. Matching the required definition matters more than preferring one setting. If you are comparing your chart with a published result, check how that source calculates quartiles before treating a small difference in box edges as a disagreement.

If no convention is specified, choose one method and use it for every comparable group. Avoid setting one chart to inclusive and another to exclusive, or switching methods between reports without noting the change. A clear chart note can say which quartile calculation was selected, especially when readers may compare the figure with results from other software.

Sample size is a practical consideration. The difference between methods is most noticeable in small, odd-sized samples, where the middle observation has a relatively large influence on the two halves. With large data sets, the quartile values may be close, but the methods are still not guaranteed to produce identical results. For even-sized samples, the simple median-of-halves split has no middle observation to include or exclude, so this particular distinction does not change the split.

How this relates to QUARTILE functions

Excel worksheet functions can calculate quartiles for checks or for a separate summary table. If the five observations 1, 2, 3, 4, and 5 are in A2:A6, =QUARTILE.INC(A2:A6,1) returns 2 and =QUARTILE.INC(A2:A6,3) returns 4. For the same layout, =QUARTILE.EXC(A2:A6,1) returns 1.5 and =QUARTILE.EXC(A2:A6,3) returns 4.5. Those results match the worked example.

Do not assume that the chart’s inclusive or exclusive setting is always interchangeable with the worksheet functions named QUARTILE.INC and QUARTILE.EXC. The functions use percentile-rank calculations, while the chart setting describes how the median is handled when splitting an odd-sized sample. For example, with four values 1, 2, 3, 4, the lower and upper halves are 1, 2 and 3, 4, whose medians are 1.5 and 3.5. The inclusive and exclusive split distinction does not change those halves, but QUARTILE.INC returns interpolated quartiles of 1.75 and 3.25. If exact agreement is important, verify how the chart calculates its quartiles rather than assuming a similarly named function will reproduce every box edge.

Common interpretation mistakes

Thinking the setting changes the overall median: It changes Q1 and Q3, not the middle of the full sorted data set.
Assuming whiskers always show the minimum and maximum: In a box plot with outlier detection, whiskers usually stop at the most extreme non-outlier observations. Separate points can represent outliers.
Calling one convention universally correct: Quartile definitions vary. State the convention and follow any standard required for the analysis.
Comparing boxes without checking sample sizes: A quartile convention can have a noticeable effect in small groups, and groups with different sample sizes should be interpreted with care.
Confusing the mean and median: A mean marker or mean line is an optional additional statistic. It does not replace the median line or determine the quartiles.

When a chart looks surprising, sort the values and calculate the median and the two half-sample medians by hand for a small group. Then check the selected quartile setting, the outlier display option, and whether Excel is treating the intended rows as observations. This quick audit is often more useful than trying to infer the calculation from the shape of a crowded chart.

Frequently Asked Questions

Which Excel box plot option should I use?

Use the method required by your course, organization, or comparison source. If no rule is specified, either is defensible; choose one, apply it consistently, and identify it when the quartile convention could affect interpretation.

Does exclusive median remove the median from the chart?

No. The option excludes the median only when Excel forms the lower and upper halves for calculating quartiles in an odd-sized sample. The overall median remains a statistic in the chart and its line can still be shown.

Do inclusive and exclusive methods differ with an even number of values?

The include-or-exclude rule makes no difference to the median-of-halves split when the sample size is even, because the values divide into equal lower and upper halves without a single central observation. Other quartile calculation methods can still produce different values, so do not assume all Excel functions and chart calculations are identical.

Why did my outlier markers change after switching the setting?

The setting can change Q1, Q3, and the IQR. Since the outlier fences are based on the quartiles and IQR, a value near a fence may be classified differently. The underlying observations have not changed; the summary rule has.

Can I reproduce the chart quartiles with QUARTILE.INC or QUARTILE.EXC?

They can match for some data sets, including the five-value example above, but they are not guaranteed to match in every case. The worksheet functions calculate percentiles using their own rank rules, so check the actual results for your sample before using them as an exact chart audit.

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.