How to Make Box Plots by Using Microsoft Excel 2007

By Jesper Knutsson

Microsoft Excel does have a lot of useful built-in chart types, such as the bar chart, scatter plot and line chart. These are handy basic charts that will cover a lot of the beginner to intermediate user needs, but for the advanced user, and for statisticians and scientists who use Excel there are some notable features lacking.

The Excel program series does not have a built in function for box plots, and the latest version, Excel 2007 is unfortunately no exception. The box plot, or stem and leaf plot, is a very powerful chart type to present data sets where the distribution of the data points are to be illustrated in an intuitive way. The standard box plot will display the 25th and 75th percentile as a box with a horizontal line denoting the median, and two “whiskers” (one on top of the box and one at the bottom) denoting the 10th and 90th percentile of the data set.

In Excel this type of chart is not found among the build in charts, but it is possible to create a box plot in excel using a couple of tricks to work around the limitations of the software. With some effort it is even possible to create box plots containing negative values, something that is not easily done using most methods.

The solution to this particular problem includes the creation of dummy series which are made invisible to create the illusion of a “hovering” box, while in reality it is just a stacked bar chart. In short, follow these steps to create your simple box plot chart:

1. Start by creating a series of data containing median, 25th percentile, 75th percentile, 10th percentile and 90th percentile data.

2. Create another table with five data series like in the picture below. The first data series will be the bottom (and later invisible) segment and is equal to the 25th percentile data series.

3. The next data series is the segment difference between the 25th percentile and the median.

4. The third segment and next data series will be the difference between the 75th percentile and the median.

5. The 10th percentile and 90th percentile data series are constructed in a analogous fashion; the 10th percentile whisker data is the 25th minus the 10th percentile =C3-E3 (and fill down).

6. The 90th percentile whisker data is the 90th minus the 75th percentile .

7. Select the range containing your data and create a Stacked Column chart using the chart wizard.

8. Add the 10th percentile whiskers by selecting the bottom segment, right click -> Format Data Series -> Y Error Bars -> Custom Minus (-) -> select the range containting your data.

9. Add the 90th percentile whiskers by selecting the top segment, right click -> Format Data Series -> Y Error Bars -> Custom Plus (+) -> select the range containting your data.

10. Select the bottom segment, right click and select Format Data Series, select Border -> none and Area -> none in the Patterns tab. The bottom segment is now invisible. After some cleaning up and simple formatting of the chart you should have a nice looking box plot.

There is also a full tutorial containing pictures available.

Jesper Knutsson is the editor of bloggpro.com, a site for excel tips.