Box and Whisker Plots.

Box and Whisker charts 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 data.

Note: there are several different ways that box and whisker plots describe sample data. The charts described in these tutorials have boxes indicating the range of the second and third (inner) quartiles of the data, with error bars indicating the first and fourth (outer) quartiles. 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.

There are other web pages that show how to make Box and Whisker charts in Excel:

• Neville Hunt, Boxplots in Excel
  http://www.mis.coventry.ac.uk/~nhunt/boxplot.htm
   
• Duncan Williamson, Box and Whisker Diagrams
  http://www.duncanwil.co.uk/boxplot.html
   

Vertical Box and Whisker Charts

This tutorial shows the procedures used to construct a vertical box and whisker plot. 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.

Horizontal Box and Whisker Charts

This tutorial shows the procedures used to construct a horizontal box and whisker plot. 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.

Example Box and Whisker Charts

BoxWhisker.zip is a zipped Excel workbook with examples of and step-by-step instructions for constructing vertically and horizontally oriented Box and Whisker plots.

Box and Whisker Chart Utility

In these pages I described techniques for constructing box and whisker charts in Excel. I have also written a Box and Whisker Chart utility that allows the user to construct box and whisker charts directly from the data; the utility builds the intermediate summary table and then creates and formats the chart from this table. The utility is designed to work in Excel versions 2000, 2002, and 2003; it has not been tested in any Macintosh version or in Excel 97 or 2007, it may or may not work on these versions, though informal testing in Excel 2007 is promising. The Box and Whisker Chart utility is a beta version, available at no cost. Ultimately it will be incorporated into a commercial Advanced Charting utility. The Box Charter dialog is shown below:

Recent enhancements to this utility (29 October 2007):

• Corrected an error that occasionally occurs when Excel closes
• Corrected treatment of outliers in horizontal box and whisker charts
• Cleaned up formatting of boxes and whiskers
• Improved treatment of unequal population sizes
• Optimized coding to reduce processing time tenfold

(27 July 2007):

• Corrected a problem with some regional settings
• Added options for chart style, including four-box and whiskers plus outliers (see below)

Download

The utility can be downloaded from here, which links to a zip file. The zip file contains an EXE file which installs the Box Charting utility.

Installation

If you installed an earlier version of this utility, you should uninstall that version before continuing. Choose Add-ins from Excel's Tools menu, locate Box Charting Utility in the list of add-ins, uncheck the box in front of the entry, and press OK.

To install the Box Charting utility, close Excel, then double click on the PTS_Box_Charter_Setup.exe file from the zip file you've downloaded. The Box Charting utility is installed as an add-in. In addition, Sample Box Chart Data.xls, a workbook containing sample data to illustrate the operation of the utility, is saved in PTS Charts, a new subdirectory in your My Documents directory.

Using the Utility

When you install the add-in, it adds a menu item called PTS Charts to Excel's Tools menu with a "Box and Whisker Chart" button. Select a range of data to chart, click this Box and Whisker Chart button, and the dialog appears.

If the selected range contains chartable data, it is indicated in the range selection box at the top of the dialog. If the selected range contains no data, or if the selection is not a range, this box is empty. If the selected range consists of a single cell, the utility identifies the range of data surrounding the active cell in the range selection box, just as the Chart Wizard does. If the selection is larger than one cell, the utility identifies only the selected range in the box. If the selected range consists of multiple discontiguous areas, you may get unpredictable results.

The output produced by this utility consists of a new worksheet with a data table and an embedded chart. The chart is a standard Excel chart, somewhat larger than the Chart Wizard's default size; it can be moved, resized, and otherwise formatted as any other Excel chart. The chart is a combination chart with a number of stacked column or bar series and an XY series.

The tabulated data is linked to the original data range, so that any changes in the data will be reflected by recalculation in the table, and redrawing of the chart.

Vertically oriented box and whisker chart (column chart)

Horizontally oriented box and whisker chart (bar chart)

Notice that the stacked columns in the chart are aligned directly above the columns of data in the worksheet. This alignment is maintained for any number of categories across the chart and table, as shown below for a larger number of categories (shown at a zoom of 75%). The sizes of horizontal box and whisker charts are also adjusted to keep the charts on screen while maintaining a reasonable aspect ratio.

Vertically oriented box and whisker chart: wide scale

Chart Styles

The latest version of the utility includes whiskers with the perpendicular line segments at their ends, and gray boxes rather than assorted light colored boxes. Since these box and whisker charts are "regular" Excel charts, they can be formatted by the user using the normal Excel formatting techniques. Note that if a bar crosses the Y=0 axis, the parts for Y>0 and Y<0 must be formatted separately.

The utility offers a simple box and whisker quartile chart (below left), in which the whiskers span the full range of the data. The utility includes a four-box version, in which the whiskers are replaced by boxes (below center). In addition, the utility offers a chart which shows outliers using asterisk and circle markers, while the whiskers extend only as far as the furthest point within 1.5 interquartile ranges below the first quartile or above the third quartile. The asterix markers show outliers within 3 interquartile ranges below the first quartile or above the third quartile, and the circles show outliers beyond 3 interquartile ranges of the first and third quartiles. The box and whisker with outliers chart style was the feature most requested by users of this utility.

Contact Me

In the event of problems with this utility, use the following link to send me an email. While I cannot answer all email messages, I will certainly address any issues that arise. If you are reporting a problem, please describe the problem as clearly as possible, and if relevant include a copy of the data and chart.

I will also consider reasonable enhancements to the utility. I do not plan to make the utility compatible with Excel 97 or earlier or with Mac versions of Excel, but I have extended its compatibility to Excel 2007. The extensive data validation required to enable multiple-area ranges prevent me from addressing this capability at this time.

Check back for news and updates.

Contact Jon about the Box and Whisker Chart utility.