Box and whisker plot in Excel

Excel 2016 and later draws box and whisker plots natively. The chart type sits with Histogram and Pareto under the statistic-chart icon in the Insert tab's Charts group: lay your data out with one column per group, select it, and insert the box and whisker chart.

That is the whole answer to the question as asked, and it takes ten seconds. The rest of this page is the part that costs people an afternoon — why the box Excel draws is often not the box someone else drew from the same numbers.

What the box actually shows

A box plot is five numbers and nothing else.

Two of those trip people up constantly. The line is the median, so a skewed process puts it off-centre in the box and that is correct, not a drawing error. And the box is a rank-based measure — it is unaffected by how far away the extreme values are, which is exactly why it is useful on messy data and exactly why it tells you nothing about the tails.

Excel can also draw the mean, as an X marker or a connecting line, in Format Data Series. Turning it on is worth doing: the distance between the mean marker and the median line is a free read on skew, and it costs nothing.

The quartile problem, which is the real question

Here is the situation that brings people to this page. You draw a box plot, your customer draws one from identical data, and the boxes differ. Nobody has made an arithmetic mistake. You are using different definitions of a quartile, and there are at least three in common circulation.

• QUARTILE.INC (and the legacy QUARTILE) puts Q1 at position 1 + (n−1)×0.25 in the sorted data and interpolates. This is Excel's default and what most software calls the standard method.
• QUARTILE.EXC puts it at position (n+1)×0.25. It excludes the median from both halves and produces a wider box. It errors on very small samples, because the required position falls outside the data.
• Tukey's hinges, which is what the original box plot was defined on. Split the sorted data at the median, then take the median of each half — including the median itself in both halves when n is odd. There is no Excel function for this; you build it with MEDIAN over two ranges.

On large samples the three converge and none of this matters. On the sample sizes people actually plot — ten, twenty, thirty readings — they diverge visibly. Ten readings:

2, 4, 5, 7, 8, 11, 12, 15, 20, 32

Three boxes of three different widths, and — the part that causes the argument — the three methods do not agree on whether the largest reading is an outlier. Under QUARTILE.EXC the whisker runs all the way to 32; under the other two, 32 is plotted as a separate point and the whisker stops at 20.

Excel exposes part of this choice on the native chart: Format Data Series offers Inclusive median and Exclusive median, corresponding to QUARTILE.INC and QUARTILE.EXC. Check which one is selected rather than assuming — and note that neither of them is Tukey's method, which is what a statistician reaching for the original definition will use.

The right response is not to argue about which is correct. None of them is wrong; they are different conventions for interpolating a quantile from a finite sample, and the disagreement shrinks to nothing as n grows. State the method on the chart or in the caption, use the same one throughout a report, and use the customer's when there is a customer. An unlabelled box plot of thirty readings is genuinely ambiguous.

The 1.5×IQR fence is a convention too

The fences sit at Q1 − 1.5×IQR and Q3 + 1.5×IQR. That multiplier is Tukey's choice, made for its behaviour rather than derived from anything.

It is worth knowing what it costs. For normally distributed data the fences land at about ±2.7 standard deviations from the centre, so roughly 0.7% of perfectly well-behaved readings fall outside them — about one in every 143. Plot 200 readings from a process that is doing nothing wrong and you should expect to see one or two points marked as outliers. They are not signals. On a box plot of 500 readings you will see a scatter of them every time.

A point outside the fence means "unusual relative to the middle half of this sample". It does not mean out of specification, it does not mean a special cause, and it is not grounds for deleting the reading. If you want to know whether something happened, the question is time-ordered and belongs on a control chart, which can tell you when.

And the detail that gets drawn wrong by hand more than any other: the whisker extends to the most extreme observation that is still inside the fence, never to the fence itself. The fence is an invisible threshold used to classify points. It is not a line on the chart.

What Excel will not let you change

The native chart gives you inclusive/exclusive median, mean markers, inner points, outlier points and gap width. Beyond that:

• The 1.5 multiplier is fixed. Some industries use 3.0 for a "far outlier"; you cannot set it.
• Tukey hinges are not available at all.
• Outliers cannot be labelled with which part or batch they were, which is usually the only thing you want to know about them.
• Box width cannot vary with n, and notched boxes — the conventional way of showing whether two medians differ — do not exist.

If you need any of those, the fallback is building the box plot from a stacked bar chart with error bars — a well-documented technique and about twenty minutes of work. Worth it for a report that will be reused, not worth it once.

What a box plot is for

Comparing several groups side by side. Four cavities, three suppliers, six months, two machines. Put them on one axis with one shared scale and differences in centre and spread are readable in a second. Nothing else does that job as compactly, which is the entire reason the chart exists.

A single box plot of one process is nearly always worse than a histogram of the same data, because the box discards the shape. Bimodal data — two machines charted as one, the classic case — and clean unimodal data can produce identical boxes. The histogram shows you the two humps immediately.

Two more things the box hides. Sample size is invisible: a box drawn from five readings and one drawn from five hundred look the same, so put n under each box. And the box says nothing about order, so a group that drifted steadily through the month and one that was stable all month give you the same picture.

Box plots are also poor at capability. A box tells you where the middle half of the parts sat; specification limits are about the tails, which is the part the box deliberately throws away. Use Cp and Cpk for that question, and read what Cpk actually claims before quoting one.

Where the spreadsheet stops

Box plots are exploratory analysis, and Excel is a reasonable place to do exploratory analysis. The limitation bites only when the comparison becomes a standing one — the same four cavities, every week, watched by more than one person. At that point what you want is a chart over time with limits that stay still, which is the argument in SPC charts without Excel.