Draw a Box and Whisker Plot in Excel

Excel doesn't offer a box-and-whisker chart. Instead, you can cajole a blazon of Excel chart into boxes and whiskers. Instead of showing the mean and the standard error, the box-and-whisker plot shows the minimum, first quartile, median, third quartile, and maximum of a set of data. Statisticians refer to this set of statistics every bit a five-number summary.

You represent each five-number summary as a box with "whiskers." The box is bounded on the pinnacle by the third quartile, and on the lesser past the first quartile. The median divides the box. How y'all lay out the chart determines the width of the box. The whiskers are error bars: One extends upwards from the 3rd quartile to the maximum, and the other extends downward from the showtime quartile to the minimum.

Notice that the median isn't necessarily in the middle of the box and the whiskers aren't necessarily the same length.

The first order of concern is to put data into a worksheet and showtime computing some statistics. The post-obit figure shows the worksheet and the statistics.

The adjacent group of statistics holds the values for the five-number summary. You can use MIN to notice the minimum value for each year, and MAX to find the maximum value. QUARTILE.INC computes the first quartile and the third quartile. Not surprisingly, MEDIAN determines the median.

The last group of statistics holds the values you put directly into the box-and-whisker plot. Why is this group necessary?

You can plough a Stacked Column chart into a box-and-whisker plot. In a stacked column, each segment'southward size is proportional to how much information technology contributes to the size of the column. In a box-and-whisker box, however, the size of a segment represents a departure between ane value and another — like the difference between the quartile and the median, or between the median and the first quartile.

And then the box is really a stacked column with three segments. The first segment is the first quartile. The second is the deviation between the median and the first quartile. The third is the deviation between the third quartile and the median.

But wait. Won't that just look like a column that starts at the 10-axis? Not subsequently yous make the offset segment disappear!

The other ii differences — between the maximum and the third quartile and between the commencement quartile and the minimum— become the whiskers.

Follow these steps after you calculate all the statistics:

Select the data for the boxes in the box-and-whisker plot.

In this worksheet, that's B21:D23. Rows 20 and 24 don't figure into this step.
Select INSERT | Recommended Charts, and then select the sixth option to add a stacked cavalcade chart to the worksheet.

The quaternary choice in the Recommended Charts is also a stacked column chart. Don't select that one. Its rows and columns are reversed.

The post-obit figure shows what the stacked column nautical chart looks similar after you insert it, delete the gridlines, move the fable, remove "Chart Title," and reformat and title the axes. The figure also shows the nautical chart toolset to right of the chart.
Add together the whiskers.

First, add the lower whiskers. With the bars corresponding to Q1 selected (the lowest portion of each stacked column), click the Plus Sign in the chart toolset. From the pop-upwards carte du jour that appears, select the Error Bars cheque box, and then the arrowhead to the right of that selection. From the resulting menu, select More Options.

This opens the Format Fault Confined console. Select the Minus radio push, the Cap radio button, and the Custom radio push.

Then click the Specify Value button to open the Custom Error Bars dialog box. Leaving the Positive Mistake Value as is, specify the cell range for the Negative Error Value. For this worksheet, that's B20:D20 (Q1-Minimum).
Clicking OK closes this dialog box, and clicking the Shut symbol closes the Format Error Bars panel.

Follow similar steps to add the upper whiskers. This time select the office of the stacked columns corresponding to Q3-Median (the upper portion of each stacked column). Then every bit earlier, click the Plus Sign in the chart toolset.

Again, select the box side by side to Error Confined in the pop-up menu, and the arrowhead to the correct of that choice. This time in the Format Mistake Bars console, select the Plus radio push button, the Cap radio button, and the Custom radio button.

Once again, click the Specify Value button to open up the Custom Mistake Confined dialog box. This time, specify the cell range for the Positive Error Value. That cell range is B24:D24 (Max-Q3). Click OK and Shut.
Brand the bottom segments disappear.

To give the appearance of boxes rather than stacked columns, select Q1 (the bottom portion of each column), so right-click and cull Format Information Series from the pop-upward carte to open up the Format Data Series dialog box.

In the Format Data Series panel, click Make full (the bucket icon), and in the Make full expanse select the No Fill radio button. And so in the Border expanse, select the No Line radio button.

Clicking Close closes the Format Data Series panel.
Reformat the remaining series to consummate the box-and-whiskers plot.

Select Median-Q1 (the portion that at present appears to be the lower part of each cavalcade), right-click and pick Format Data Series from the pop-up menu. In the Format Data Serial console, select Fill and select the No Fill radio button in the Fill area. Then select the Solid Line radio button in the Border expanse.

Side by side select Border Color and select the Solid Line radio push. Click the Colour Button and select black from the Theme Colors palette.

Finally, select Q3-Median (the upper portion of each column), and then get through the aforementioned sequence.

Afterward that, delete the legend. Yous tin can add another data series that shows where the means are, and another that would allow me to connect the medians, but this is enough for now.

Notice that after you lot finish working with the Format Data Series panel for one data serial, yous tin can leave information technology open up. Then select another data serial in the chart and kickoff formatting it. Unlike earlier versions of Excel (that worked with dialog boxes rather than panels), you don't have to close the formatting panel and reopen it each fourth dimension you want to format a information series.

Therry Thattely

Draw a Box and Whisker Plot in Excel

Well-nigh This Article

This article tin be found in the category:

Menu Halaman Statis