Box and Whisker Plots
Box and Whisker earns its place when several groups have to be compared on one scale, each box showing level and spread at once. That is the comparison a bar chart of averages quietly withholds: two teams with the same mean handling time can have wildly different consistency, and only one of these two charts will admit it. Excel needs the data laid out for that comparison, so give it a category column beside a value column, or one column per group whose header carries that group's name, and include those headers in the selection or you will get a single box drawn from everything at once. The one setting to settle before quoting a figure off the finished chart is the quartile convention, because Format Data Series carries an inclusive and an exclusive method and they place the top and bottom edges differently on a small sample. The rest of that panel only changes what is displayed.
The formula
=MEDIAN(B2:B11) the line drawn inside the box
=QUARTILE.INC(B2:B11,1) Q1 as the chart draws it by default
=QUARTILE.INC(B2:B11,3) Q3, likewise — the top of the box
=QUARTILE.EXC(B2:B11,1) Q1 under the exclusive method
=QUARTILE.INC(B2:B11,3)-QUARTILE.INC(B2:B11,1) the interquartile range; 1.5 times it sets the whiskersA worked example
B2:B11 holds ten support-ticket resolution times in hours: 12, 14, 15, 15, 16, 18, 19, 21, 22 and 41.
Select B1:B11, Insert > Insert Statistic Chart > Box and Whisker, then check the drawing against =QUARTILE.INC(B2:B11,1), =MEDIAN(B2:B11) and =QUARTILE.INC(B2:B11,3).
Under the inclusive convention the edges are 15 and 20.5 and the median is 17, so the box is short with its median line low inside it and a mean marker out at 19.3, dragged up by one slow ticket — level and spread disagreeing is exactly what the format is for. The interquartile range is 5.5, so 1.5 of them reaches 28.75: the bar therefore stops at 22, the largest value under that threshold, and the 41 is drawn as a lone dot above it. Point the same ten numbers at the exclusive convention, in the chart or via =QUARTILE.EXC, and the edges move to 14.75 and 21.25 — a visibly taller box from data that never changed.
Which one do I need?
| If you want to… | Use |
|---|---|
| Several groups whose spread, not merely their average, needs comparing | Insert > Insert Statistic Chart > Box and Whisker, with the category column and the value column both in the selection |
| One group, and the question is the shape — two peaks, a long tail, a gap | A histogram. A box conceals shape entirely: a two-humped set and an even one can produce identical boxes |
| The quartile edges disagree with the ones your course or report uses | Format Data Series carries both conventions; the exclusive one matches QUARTILE.EXC and the inclusive one matches QUARTILE.INC, so pick whichever the surrounding analysis already uses |
| The separate dots are unwanted and the bars should span everything | Untick Show outlier points in Format Data Series, after which each bar runs to the true extremes of its group |
| The mean marker is unwanted, or every observation should appear beside its box | Show mean markers, Show mean line and Show inner points are three independent tickboxes on the same panel |
| Excel 2013 or earlier, where the chart type does not exist | A stacked column of Q1, median-minus-Q1 and Q3-minus-median with the bottom segment made invisible, plus error bars — or simply publish the five figures as a table |
| Google Sheets, which has no equivalent chart type at all | A candlestick chart fed from a helper table of low, Q1, Q3 and high is the usual stand-in |
| Spread wanted as a single figure rather than a picture | =STDEV.S(range) for the standard deviation, or subtract two QUARTILE calls for the interquartile range |
| Each observation is a pair of numbers rather than one | A scatter chart — a box summarises one variable at a time and has nowhere to put the second |
Frequently asked questions
Do the ends of the bars show the smallest and largest values?
Usually not, and this is the most common misreading. Each bar stops at the last value within 1.5 interquartile ranges of the box edge, and anything further out is plotted as its own dot instead, so the real extreme of the group is that dot rather than the bar end. Untick Show outlier points in Format Data Series if you genuinely want the bars to span the full range.
Why are the quartiles different from the ones I calculated by hand?
Because there is more than one defensible way to split a sample into quarters when the count is odd, and Excel implements two of them. Take the nine values 2, 4, 6 up to 18: one convention gives a lower edge of 6 and an upper of 14, the other gives 5 and 15 from exactly the same numbers. The chart switches between them in Format Data Series, and QUARTILE.INC and QUARTILE.EXC are the two matching worksheet functions.
Why does my chart draw one box instead of one per group?
Everything landed in a single series. Either all the values sit in one column with no column identifying the group, or the group names were left outside the selection when the chart was inserted. Fix the layout so each group has its own column with its name in the header row, or add a category column beside the values, then reselect including the headers and insert again.
Can I make one of these in Google Sheets?
Not directly — Sheets has no such chart type. The usual workaround is a candlestick chart driven from a small helper table holding the low, lower quartile, upper quartile and high for each group, computed with MIN, QUARTILE, QUARTILE and MAX. It draws boxes and bars but calculates no outliers, so extreme values stay hidden inside the bars rather than being separated out.
New guides and tools, once a month
DE + EN · double opt-in · no spam