Peltier Technical Services, Inc.
 

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


Peltier Tech
Chart Add-Ins

Peltier Tech Waterfall Chart Utility
Peltier Tech Cluster-Stack Chart Utility
Peltier Tech Box and Whisker Chart Utility
Peltier Tech Marimekko Chart Utility
Peltier Tech Dot Plot Utility
Peltier Tech Cascade Chart Utility

 

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 ExcelThis 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.

 

 

Page copy protected against web site content infringement by Copyscape

 

Peltier Tech Waterfall Chart Utility Peltier Tech Cluster-Stack Chart Utility Peltier Tech Box and Whisker Chart Utility Peltier Tech Marimekko Chart Utility Peltier Tech Dot Plot Utility Peltier Tech Cascade Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.


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

Microsoft Most Valuable Professional

My MVP Profile