Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | Peltier Tech Blog

Peltier Tech
Chart Add-Ins

Excel Dashboards

Books at Amazon.com

How to Make Vertical Box and Whisker Charts (Box Plots) in Excel.

Peltier Tech Box and Whisker Chart Utility for Excel This tutorial shows how to create Box and Whisker Charts in Excel, including the specialized data layout needed, and the detailed combination of chart series and chart types required. This manual process takes time, can be prone to error, and becomes tedious.

I have created the Peltier Tech Box and Whisker Chart Utility for Excel to create such charts automatically from raw data. This utility, a standard Excel add-in, lays out data in the required layout, then constructs a chart with the right combination of chart types. This is a commercial product, tested on hundreds of machines in a wide variety of configurations, which saves time and aggravation.

The Peltier Tech Box Plot Utility creates charts in either horizontal or vertical orientation. It provides four different methods for calculating quartiles. This add-in also provides three styles for the box plots, including one that shows outliers.

Please visit the Peltier Tech Box and Whisker Chart Utility page or the Peltier Tech Box and Whisker Chart Utility Documentation page for more information.

Box and Whisker Charts (Box Plots) are commonly used in the display of statistical analyses. Unfortunately, Microsoft Excel does not have a built in Box and Whisker chart type. You can create your own custom Box and Whisker charts, using stacked bar or column charts and error bars, in combination with line or XY scatter chart series to show additional features.

This tutorial shows the procedures used to construct a vertical box and whisker plot in Excel. In this example, the boxes show medians and upper and lower quartiles of the data, while the whiskers indicate the minimum and maximum values. This tutorial also shows how to add another series representing the means of the data.

Note: there are several different ways that box and whisker plots depict sample data. The whiskers may alternatively show a more rigorously defined upper control limit, and special markers may be used to indicate outliers beyond these. Any such alternatives are beyond the scope of this exercise.

Data Layout for the Box and Whisker Chart (Box Plot)

We'll start with the following statistical data, which was calculated from sample data (not shown here):

	A	B	C	D	E
1		Lot Number
2		A	B	C
3	Mean	34.34	35.11	35.55
4	SD	3.04	2.49	2.94
5	Median	34.37	35.00	36.10
6	Q1	32.33	33.78	33.37
7	Q3	36.33	36.85	37.67
8	Minimum	25.70	28.53	28.00
9	Maximum	41.90	42.40	41.87
10	25th Pct	32.33	33.78	33.37	Box1
11	50th Pct	2.04	1.22	2.73	Box2
12	75th Pct	1.96	1.85	1.57	Box3
13	Min	6.63	5.25	5.37	Box1Minus
14	Max	5.57	5.55	4.20	Box3Plus

Mean	Average of the data to be plotted, `AVERAGE(`{data}`)`
SD	Standard deviation, `STDEV(`{data}`)`
Median	Median of the data, `MEDIAN(`{data}`)`
Q1	First quartile, `PERCENTILE(`{data}`,0.25)`
Q3	Third quartile, `PERCENTILE(`{data}`,0.75)`
Minimum	Minimum value, `MIN(`{data}`)`
Maximum	Maximum value, `MAX(`{data}`)`
25th Pct	Plotting value of first quartile = Q1
50th Pct	Plotting value of median = Median - Q1
75th Pct	Plotting value of third quartile = Q3 - Median
Min	Lower error bar length = Q1 - Minimum
Max	Upper error bar length = Maximum - Q3

If any of the values in this table are negative, then a more complicated data layout is required. This arrangement is described in Stacked Column Charts that Cross the X Axis and applied to waterfall charts in Waterfall Charts that Cross the X Axis.

Creating the Box and Whisker Chart (Box Plot)

Select the Green cells (A2:D2), hold down Ctrl while selecting the Yellow cells (A10:D12). A discontiguous range is now selected. Run the Chart Wizard and create a stacked column chart.

Don't select a stacked 100% column chart, which normalizes values to a total stack height of 100%.

Let's do a little simple formatting. Format the plot area: White fill, Black border. Remove the gridlines. Remove excess decimal digits in the value axis number format.

Now we'll add the error bars for min and max values. Select the "75th Pct" Series (light yellow), and press Ctrl-1 (numeral "one") to open the Format Series dialog. Select the Y Error Bars tab. Click in the Custom + (plus) box, and with the mouse choose the Orange colored range (B14:D14). Select the "25th Pct" Series (dull blue), press Ctrl-1 (numeral "one") to open the Format Series dialog. Select the Y Error Bars tab. Click in the Custom - (minus) box, and with the mouse choose the Pink colored range (B13:D13).

Set the minimum scale of the value axis to 20 to better fit the display. Make the "25th Pct" series invisible: format it (Ctrl-1) to have No Border, No Area Fill.

Select the Green and Blue ranges (A2:D3), Copy. Select the chart (not a series), Paste Special with the following options: Add Series, By Row, Series Names in First Column, Categories in First Row.

Right click the new series, select Chart Type... and Choose the Line Chart style, Line with Markers substyle.

Format the remaining series according to your favorite formats.

To add another set of markers, indicating averages from another population, values from a particular sample, or a set of targets, add the new series name and values below the data table (A15:D15). Add the series as you added the Mean above. Select the green range (A2:D2), then hold CTRL while selecting the added data in A15:D15, copy this two-area range, select the chart, and use Edit menu > Paste Special with the following options: Add Series, By Row, Series Names in First Column, Categories in First Row. You can add the new series as another set of markers.

You can also add the series as a horizontal line segment to denote some kind of target. First, change the new series to an XY type (Chart menu > Chart Type > XY Type), format the series to display no markers or lines, but add short positive and negative error bars. The error bars shown have a Fixed Value of 0.2, but you may need to adjust yours to get the desired appearance.

BoxWhiskerH.html shows a similar procedure to construct a horizontally oriented Box and Whisker plot. BoxWhisker.zip is a zipped Excel workbook with examples of and step-by-step instructions for vertically and horizontally oriented Box and Whisker plots. A commercial utility that constructs Box and Whisker charts is described in PTS Box and Whisker Chart Utility.

Brian Kelly, of Dublin, Ireland, has suggested a modified Box and Whisker chart. This version has two sets of error bars, one indicating the 10th and 90th percentiles, the other showing the minimum and maximum values. The min and max error bars, drawn here without the cross at the end, consist of the error bars shown in my exercise. The 10th and 90th percentile error bars, shown here with crosses, are constructed in the same manner, and applied to the Median series.

PTS Box and Whisker Chart Utility for Excel

In this article I describe techniques for constructing box and whisker charts (box plots) in Excel. I have also developed the PTS Box and Whisker Chart Utility for Excel that allows a user to construct box and whisker charts directly from the raw Excel data. The utility builds the intermediate summary table and then creates and formats the box plot from this table. This utility is designed to work in Excel for Windows versions 2000 through 2007. The utility has not been tested in any Macintosh version or in Excel 97; it is not expected to work in these versions. The Box and Whisker Chart dialog is shown below:

Read about the PTS Box and Whisker Chart Utility for Excel.

�

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2010. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile