How to Make Box and Whisker Plots (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.

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. These sources predate mine, and the techniques are not as extensive.

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

How to Make Vertical Box and Whisker Charts in Excel

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.

How to Make Horizontal Box and Whisker Charts in Excel

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.

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