Boxplots in Excel

by Neville Hunt, Coventry University

Acknowledgements

An earlier version of this article was published in The Spreadsheet User Volume 3, Number 2, November 1996.
I am grateful to Rodney Carr for greatly simplifying my original method and to Duncan Williamson for some helpful advice.
I am grateful to James Small of Rollins College for supplying the instructions for Excel 2008 for Mac users.

If you find this article useful, why not subscribe to Teaching Statistics magazine, where I regularly contribute articles on drawing statistical charts using Excel.

Introduction

A boxplot, or box and whisker diagram, provides a simple graphical summary of a set of data. It shows a measure of central location (the median), two measures of dispersion (the range and inter-quartile range), the skewness (from the orientation of the median relative to the quartiles) and potential outliers (marked individually). Boxplots are especially useful when comparing two or more sets of data. Regrettably, there is currently no boxplot facility in Microsoft Excel. For simplicity, many recent statistics textbooks (for example, Daly et al, 1995) omit the fences used to identify possible outliers. These simplified boxplots, displaying most of the important features, can be drawn quite easily in Excel.  In the absence of any fences (see Devore and Peck (1990) for a definition), a simple rule is that a whisker which is longer than three times the length of the box probably indicates an outlier.

Method

Suppose we have data from three groups, A, B and C. Calculate the statistical functions QUARTILE(,1),  MIN, MEDIAN, MAX and QUARTILE(,3) in that order for each data set. Arrange the results on an Excel worksheet as shown below.

In Excel 5/95:

• Highlight the whole table, including figures and series labels.
• Use Chart-Wizard - Line - Option 7 - Data in Rows - Finish to produce something like the chart below.  Option 7 plots all the series as symbols without connecting lines, but also includes high-low lines which connect the maximum and minimum points for each group.

• Now activate the chart and select Format - Chart Type - Options - Options - Up-Down Bars - OK
  The outcome should be a set of boxplots, as shown below.  The essential feature of up-down bars is that they connect the first and last series - hence the rather strange ordering of the statistics in the table!

In Excel 97/2000/2003:

• Highlight the whole table, including figures and series labels, then click on the Chart Wizard.
• Select a Line Chart.
• At Step 2 plot by Rows, (the default is Columns), then Finish.
• Select each data series in turn and use Format Data Series to remove the connecting lines.
• Select any of the data series and Format Data Series; select the Options tab and switch on the checkboxes for High-Low lines and Up-Down bars.

In Excel 2007:

• Highlight the whole table, including figures and series labels, then select Insert from the main menu.
• Under Charts select a Line chart and choose the Line with Markers option.
• Under Chart Tools select Design > Switch Row/Column.
• Right-click on a data point from the first data series, and choose Format Data Series > Line Colour > No line to remove the connecting lines.  Repeat for the other four data series in turn.
• Select any of the data series and under Chart Tools select Layout > Analysis > Lines > High-Low Lines, then Layout > Analysis > Lines > Up/Down Bars > Up/Down Bars.
• Further customising can be carried out according to your own preferences by right-clicking on the relevant object and selecting the Format option on the shortcut menu.

• Set up the table as in PC instructions above (q1, q0, q2, q4, q3)
• Highlight the whole table, including figures and series labels, and then select the Charts menu.
• Select a Line chart and choose the Marked Line option.
• On the formatting palette choose Chart data, Edit ... Sort by rows.
• Click on a data point from the first data series, and from the formatting palette choose Colors, Weights, and Fills
• Under Line Color, select No line. Repeat this process for the other four data series in turn.
• Double click on any of the data series and you should get the Format Data Series menu.
• Choose Options on this menu and check the boxes marked High-Low Lines and Up/Down Bars. Hit OK.

References

Daly, F, Hand, D J, Jones, M C, Lunn A D and McConway, K J (1995). Elements of Statistics. Addison Wesley / The Open University.

Devore, J and Peck, R (1990). Introductory Statistics. West Publishing Co.

This page is maintained by Neville Hunt.
Last updated 18 January 2010 .