Box and whisker plots, side by side

One column per group, boxes side by side. Quartiles, the 1.5×IQR fences, whiskers that stop at real readings and outliers drawn individually — with the quartile method stated, because that is why your plot and Excel's disagree.

How you draw a box plot in Excel

Excel 2016 and later draws them natively. Lay the data out with one column per group, select it, and pick Box and Whisker from the statistic-chart icon in Insert → Charts. Turn on the mean marker in Format Data Series while you are there — the distance between it and the median line is a free read on skew.

The box Excel draws may not be the box your customer drew from the same numbers. Its box and whisker chart offers you both an inclusive and an exclusive median in Format Data Series — the QUARTILE.INC and QUARTILE.EXC definitions — and Tukey’s original hinges are a third answer again. On a sample of a dozen readings the difference is plainly visible, and nobody has made an arithmetic mistake.

Which quartile definition yours is using, and how to match theirs

Nothing you paste leaves this calculation. It is not stored, not logged and not sent anywhere else — the numbers are computed and returned, and that is all. Paste straight out of a spreadsheet — tab-separated columns are understood as they land.

What the box actually shows

Not what most people assume. The box is built from quartiles, not from the mean and standard deviation:

The line in the box is the median, not the mean. Half the readings sit either side of it.
The box spans Q1 to Q3 — the middle half of the data. Its height is the interquartile range.
The whiskers reach the most extreme reading still inside the fences at Q1 − 1.5×IQR and Q3 + 1.5×IQR. They stop at a real data point, never at the fence itself.
Anything beyond a fence is drawn individually as an outlier.

Because it is built from ranks rather than from arithmetic on the values, a box plot is largely unmoved by an extreme reading — which is the property that makes it good at comparison and bad at showing shape.

Why your box plot does not match Excel's

This is the commonest complaint about box plots, and it is not a bug in either tool.

There is more than one defensible way to compute a quartile from a finite sample, and the common definitions disagree on small samples. Excel offers QUARTILE.INC and QUARTILE.EXC; the exclusive-median method that statisticians usually reach for is a third; there are several more in the literature. On ten readings they can differ enough to disagree about whether a given point is an outlier — which changes the picture, not just the third decimal place.

The right response is not to argue about which is correct, because none of them is wrong. They are different conventions for interpolating a quantile, and they converge as n grows. State which method you used, use the same one throughout a report, and use your customer's when there is a customer. This tool tells you which it used, in the results above, for exactly that reason.

The 1.5 is a convention too

John Tukey chose 1.5 because it works, not because it was derived. On perfectly normal data the fences land at about ±2.7 sigma, so roughly seven readings in a thousand fall outside them by chance alone. On a hundred readings you should expect to see the odd "outlier" that is nothing of the kind.

An outlier on a box plot is a prompt to go and look, not a verdict. Plenty are real measurements from a process that occasionally does that, and deleting them because a chart drew them as dots is how a genuine signal gets tidied away.

When not to use one

For a single process, a histogram is usually better. A box plot deliberately discards shape, and the thing it hides most completely is bimodality: two distinct populations — two spindles, two suppliers, two shifts — produce a box plot that looks entirely unremarkable while the histogram of the same readings shows two obvious humps. That is precisely the finding you were looking for.

Box plots earn their keep when there are several groups to line up on one axis. Four machines, three suppliers, six months. If you have one group and one question, draw the histogram.

And note what a box plot cannot tell you at all: anything about time. Ten readings in the order they were made can trend steadily upward and produce exactly the same box as ten readings in random order. If the readings came off a process in sequence, a control chart answers a question the box plot cannot even ask.

What the Excel version will not do

The time order is gone, and that is the point of the chart: A box plot pools every reading into one shape. A process that drifted steadily from the start of the shift to the end and a process that never moved can draw the identical box — so the one thing a box plot cannot show you is the one thing that tells you whether to stop the line.
Next month is a new chart, not the next point: Rebuilding the box from a new range each month means nobody can see that the median has been sliding for six of them. The comparison you want is across time, and the chart is built to compare across groups.
Overlap is not a test: Two boxes that barely overlap may not be significantly different, and two that overlap heavily may be. The picture invites the conclusion and does nothing to check it.

Questions people ask about this

What does a box and whisker plot show?

The median as the line inside the box, the middle half of the data as the box itself, whiskers reaching the most extreme reading still inside the fences at 1.5 times the interquartile range, and anything beyond those drawn individually. It is built from ranks, not from the mean and standard deviation.

Why does my box plot not match Excel?

Because there is more than one defensible way to compute a quartile from a finite sample, and the common definitions disagree on small samples — Excel offers two of them and statisticians often use a third. On ten readings they can disagree about whether a point is an outlier at all. State which method you used; this tool tells you which it used.

Where does the 1.5 in the fences come from?

Tukey chose it because it works, not because it was derived. On normal data the fences land at roughly 2.7 sigma, so about seven readings in a thousand fall outside them by chance. An outlier on a box plot is a prompt to go and look, never a verdict.

When should I use a histogram instead?

Whenever you have one group. A box plot deliberately discards shape, and what it hides most completely is bimodality — two spindles, two suppliers, two shifts produce an unremarkable box while the histogram shows two obvious humps. Box plots earn their keep comparing several groups on one axis.

A box plot throws the time order away.

Put the same readings on a control chart and the order comes back — the drift a box plot cannot show is exactly what the chart is for. Same paste, except it keeps charting as the next shift reports in, against limits frozen at a baseline you locked.

Start now See it on live data

More quality tools tools

Pareto chart generator — Categories and counts in, ranked bars and the cumulative line out — with the vital few named, an "Other" bucket that always sorts last, and an honest warning when the distribution is flat and there is no dominant cause to attack.
Histogram and normality test — A histogram with the fitted normal curve, both standard bin rules with a reasoned recommendation, and an Anderson-Darling test that refuses to tell you your data is normal — because no test can.

Everything else

Cp / Cpk calculator — Paste measurements — or type a mean and a sigma — with your tolerance, and get Cp, Cpk, Pp, Ppk, the sigma level and the expected parts per million out of spec.
Control limit calculator — Give it subgroups or individual readings and it returns the control limits for the chart, the range or sigma chart beneath it, and every constant it used to get there.
Control chart generator — Paste a column of numbers, or rows of subgroups, and get a real control chart: limits from the data, Nelson rules 1–4 evaluated, out-of-control points marked.
Nelson & Western Electric rules checker — Every run rule evaluated on your data, each violation named in plain English with what it usually indicates — a shift, a trend, tool wear, two machines mixed.
Cpk confidence interval & sample size — A Cpk of 1.33 from 30 pieces has a 95% interval of roughly 0.97 to 1.69. See the uncertainty in your own number, and how many parts would settle it.
Cpk ↔ PPM ↔ sigma level converter — What PPM is a Cpk of 1.33? What Cpk does 3.4 PPM need? Both conventions shown side by side, because the 1.5 sigma shift is why two sources disagree by a factor of ten.
Control chart constants — The whole table, n = 2 to 25, with the formula each constant belongs to. The same values the charts on this site are computed from.
X̄-R chart calculator — Subgroups in, X̄ and R charts out — limits from A₂, D₃ and D₄, run rules evaluated, and the range chart shown first because it decides whether the averages chart can be trusted.
X̄-s chart calculator — For subgroups big enough that the range wastes them. Limits from A₃, B₃ and B₄, sigma recovered with the c₄ correction, and the s chart read first because it decides whether the averages chart can be trusted.
I-MR chart calculator — For processes that give you one number at a time — a batch, an oven, a destructive test. Limits from the mean moving range, run rules evaluated, and an honest note about what an individuals chart cannot see.
Levey-Jennings chart with Westgard rules — Paste your QC results with the assigned mean and SD from the package insert, and get the chart with every sigma band drawn and all six Westgard rules evaluated — each one named, and labelled random or systematic error.
Bland-Altman plot — Paired readings from two methods, plotted as difference against average — with the bias, the limits of agreement, their confidence intervals, and a test for whether the disagreement depends on the magnitude.
Gage R&R calculator (ANOVA) — Paste a crossed study — part, operator, reading — and get the full ANOVA: repeatability and reproducibility separated, the part-by-operator interaction tested rather than assumed away, %GRR, %Tolerance and ndc against AIAG's bands.
p, np, u and c chart calculator — Counted data rather than measured: proportion defective, number defective, defects per unit. Limits that step with the sample size instead of pretending it never changed, and an honest warning when the counts are too low for three-sigma limits to mean anything.
Grubbs outlier test — The G statistic against its critical value, the p-value, and the point implicated — plus a straight answer about what to do next, which is almost never to delete the reading.
Scatter diagram and correlation — Paired readings plotted, with Pearson r, Spearman rho, r², the least-squares line and its p-value — and the two reported together, because when they disagree the disagreement is the finding.
Standard deviation calculator — The usual summary statistics, and the thing a spreadsheet will not tell you: the within-subgroup sigma a control chart uses, beside the overall sigma a textbook means. The gap between them is why Cpk and Ppk disagree.