What the heck is a box and whisker chart, and why would you need one? Well, I’m not a statistician, but here’s my overview.

A box and whisker chart can give you a snapshot of each series in your data. What are the highest and lowest numbers? What was the median number? What was the range of numbers on either side of the median?

Sales in a Line Chart

For example, you’re in the retail business, and have three stores. Store03 opened at the beginning of the year, and got off to a slow start, but did very well at the end of the year. A line chart lets you compare the store sales over the year.

Sales in a Box and Whisker Chart

Instead of showing data over time, a box and whisker chart shows the data in numerical order, divided into 4 equal sections (quartiles). For each store’s sales, you can calculate the MIN, MEDIAN and MAX. Then, calculate the median for each half, to find the lower quartile (Q1) and upper quartile (Q3).

Parts of a Box and Whisker Chart

From those five numbers, you can build a stacked bar or column chart with error bars, to create a box and whisker chart. For example, the left whisker starts at the minimum value, and stops at the first quartile.

Excel doesn’t have a Box and Whisker chart type, so I built one by following the box and whisker chart instructions on Jon Peltier’s web site. The chart shows the narrow range of values in Store01, compared to the wide range at Store03.

Box plots don’t always show perfect symmetry, but Store 3 has a strange box plot appearance because of its very low sales in the first half of the year. Note the difference from the 7th smallest value (12) and the 8th smallest (80). It’s as if there were two stores, one averaging 6 for 7 months, and one averaging 90 for 5 months.

Jon Peltier’s Box and Whisker Chart Utility

As I mentioned, Excel doesn’t have a Box and Whisker chart type, so charting expert, Jon Peltier, has developed a utility to help you create one. The Box and Whisker Chart Utility eliminates the long and complicated manual process that you’d have to follow to build a box and whisker chart yourself.

There’s a free demo version and a professional version, which is the one that I installed. It creates a new tab on the Ribbon with a Box and Whisker Plot command.

Then, select a block of data and headings, and click the button to start the utility. Select from the options for Orientation and Style, then click OK to create the chart.

A new sheet is inserted in the workbook, with the chart data, the chart, and some controls to let you adjust the chart. The original data remains on its sheet, unchanged.

With the Box and Whisker Chart Utility, it took about 10 seconds to create and format this box chart, compared to the 10 minutes or so that it took me to set up my own chart.

If you need to make box and whisker charts, the PTS Box and Whisker Chart Utility will make it much easier and quicker for you to create them. Try the free demo or buy the professional version for more features.

________________________________