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 Horizontal 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 horizontal 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
15	Offset	0.833333	0.5	0.166667

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
Offset	Provides appropriate height to Mean values on horizontal version of chart.

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. These were written for column charts but the concepts are applicable to bar charts as well.

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 bar chart.

Don't select a stacked 100% bar chart, which normalizes values to a total stack value 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. Format the Scale of Category Axis with these options: Categories in Reverse Order, Value Axis Crosses at Maximum Category.

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). These horizontal error bars are Y axis error bars, because a bar chart's Y axis is horizontal, not vertical.

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 Blue range (A3:D3), then hold down CTRL while selecting the Purple range (A15:D15), Copy. Select the chart (not a series), use Edit menu > 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 XY Scatter Chart style, Scatter with Datapoints Connected with Lines.

Format the Secondary Category Axis (top of chart) scale with the option, Value (Y) Axis Crosses at Maximum Category. Format the Secondary Value Axis (right of chart) so the Minimum Scale = 0 and the Maximum Scale = 1.

Format the Secondary Value Axis (right side of chart) patterns, selecting None for Major and Minor Tick Marks and Tick Labels. Right click the chart, select Chart Options, and on the Axes tab, Uncheck Secondary Category Axis.

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, it's useful to note the series formula of the last series added:

    =SERIES(Sheet1!$A$15,Sheet1!$B$3:$D$3,Sheet1!$B$15:$D$15,6)

The Mean values are in Sheet1!$B$3:$D$3, and the offsets defining vertical position of the markers are in Sheet1!$B$15:$D$15. Add your new series values at the bottom of the table (B16:D16), select the Mean series, copy the series formula, select the Chart Area, click in the Formula Bar and paste, then edit the formula to read as follows, and press Enter:

    =SERIES(Sheet1!$A$16,Sheet1!$B$16:$D$16,Sheet1!$B$15:$D$15,7)

Excel makes the new series an XY series, since the last series you added was changed to XY, so you don't have to follow the extra several steps to make it look right. Add any additional series in the same manner. The added series could be added as another set of markers, or without markers but with short positive and negative Y error bars. In this case the error bars used a fixed value of 0.075, but you'll have to adjust this based on the number of categories.

BoxWhiskerV.html shows a similar procedure to construct a vertically 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 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