I have developed a Box and Whisker Chart Utility that allows the user to construct box and whisker charts directly from the raw 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 through 2007.

Output from the Box and Whisker Utility consists of a table of data and a chart. The utility produces three styles of box plots: Simple Box and Whisker Quartile Chart, Four Box Quartile Chart, and Box and Whisker Chart with Outliers.

Each of these box and whisker chart styles is available in vertical (column) and horizontal (bar) variations.

The Box and Whisker Utility computes quartiles in four different ways, which can be changed on the fly by the user: Tukey hinges, CDF quartiles, Minitab quartiles, and Excel quartiles. The Tukey and CDF techniques are generally close to each other (identical in the data set below and therefore shown in just the first chart), Minitab calculates the widest interquartile ranges (fewer outliers), and Excel calculates the narrowest interquartile ranges (more outliers).

The user can toggle the display of averages by checking the checkbox.

The user can toggle the display of outliers in a Box and Whisker Chart with Outliers by checking the checkbox.

All of these box and whisker charts are “normal” Excel charts, made from a combination of common chart types, so they can be resized, reformatted, and copy-pasted into other files and applications. The user should make a copy of a chart, and make changes to the copy, to prevent the loss of work if the only chart is edited beyond recognition.

The core of the PTS Box and Whisker Utility is a procedure called PTS_BoxWhiskerChart, which can be called from other VBA procedures. The syntax of the PTS_BoxWhiskerChart function is:

Function PTS_BoxWhiskerChart(rngData As Range, _
                             bByColumn As Boolean, _
                             bFirstLabels As Boolean, _
                             bVertical As Boolean, _
                             lBoxWhiskerChartStyle As BoxWhiskerChartStyle) _
                             As Chart
 

There is a free demo version of the utility which creates simple vertical box and whisker style charts without the wide range of options described above. More details about the utility can be found in the PTS Box and Whisker Plot Utility Documentation. A quick comparison of the demo and professional versions of this utility can be found at PTS Box and Whisker Plot Utility, where the utility can also be purchased.

Related Posts:

• Box and Whisker Chart Median Options
• Charting Utility Upgrades
• Simple Box Plots
• Waterfall Chart Utility
• Box Plot of Values Against Limits
• Introducing PTS Dot Plot Utility
• LOESS Utility – What the Buttons Mean
• LOESS Utility – Awesome Update
• Clustered-Stacked Column Chart Utility
• PTS Blog Inaugural Post