Last fall I released my Box and Whisker Chart Utility, which fills a need for such a chart in Excel. It’s been somewhat popular, and after some frantic bug fixing the first week or so, there have been no further bug reports. There was one design decision I made early on which was not satisfactory to myself at the time, and at least one user has found issues with it. The issue is that the median is represented by a gap between the second and third quartile boxes.

Box Plot with Median Indicated by Gap

The problem with the gap is that it’s hard to control its thickness closely. There is some variation from one category to the next, due I expect to rounding errors in pixel counting. If the interquartile range is small, i.e., less than twice the gap thickness, the boxes may not appear at all. Also in an earlier beta version, if the median was near zero, the boxes may appear incorrectly above or below the horizontal axis. But the decision to choose this technique was not as silly as this makes it sound.

The first box plot program I came up with, about three or four years ago, used boxes that had borders. This provides a somewhat conventional appearance, and the median is obvious.

Box Plot with Borders on Quartile Boxes – Excel View

I didn’t like this approach, however, because when Excel stacks column series, the border between stacked columns is twice as thick as the border around a single column. When the charts were copied and pasted as pictures, or exported as image files, this double border reverted to a single border (see below), but it made me crazy in the actual charts.

Box Plot with Borders on Quartile Boxes – Export View

An intermediate version of the utility removed the borders, and used different colors for the second and third quartile boxes. The problem with this approach was deciding on two colors which do not clash (e.g., not red and green) and have approximately the same visual effect (i.e., not light gray and black). And oh yeah, the colors have to be included in the user’s color palette.

Box Plot with Different Colored Quartile Boxes

So I scrapped the quartile boxes with borders and the different colored quartiles in favor of the median gaps. The gaps addressed the problems of the double thickness borders and of the different colored boxes, and if the user changed the background color of the chart, the gap was filled with the background color. Of course, everyone should want a white background.

I could have lived with the issues mentioned above (irregular gap thickness, obscuring of thin boxes), until a customer pointed out another issue with the gaps. If you convert to a logarithmic Y axis scale, the default gap is narrow at the top of the chart and wide at the bottom. The following chart exaggerates the effect.

Logarithmically Scaled Box Plot with Median Indicated by Gap

Since I now can’t live with the median gap approach, I need to select another way to display the median. Another way to show the median is with a horizontal line, which I thought I had tried, but I can find no residue of such trials. You can use a dark line or a light line.

Box Plot with Median Indicated by Dark Line

Box Plot with Median Indicated by Light Line

The light line looks like the gap, except the pixel counting for a line produces consistent line thicknesses, at least until Excel 2007, where antialiasing runs amok. But I think I prefer the dark line.

I could combine the horizontal line with a bordered box that spans both interior quartiles, giving the appearance of the bordered quartiles without a double border at the median.

To help answer this question, I’m asking my readers:

What technique should the Box and Whisker Chart Utility use to indicate the median value? Should more than one option be offered?

• Gap between Quartile Boxes
• Border on Quartile Boxes
• Differently Colored Quartile Boxes
• Dark Line at Median
• Light Line at Median
• Dark Line and Dark Interior Quartile Box
• Something Else — Please Describe

Any of these alternatives will be fairly quick to implement, and any customers will have the option to upgrade for only the price of an email.

Update 27 June 2009

Sometime late in May or early in June, I decided to switch to a discrete line to encode the median, and made the needed adjustments to my utility. This is the next-to-last option shown above. Using this approach actually simplified the utility.

Related Posts:

• Charting Utility Upgrades
• Simple Box Plots
• Announcing the Box and Whisker Chart Utility
• 9 Steps to Simpler Chart Formatting
• Using Colors in Excel Charts
• Contour and Surface Charts in Excel 2007
• Box Plot of Values Against Limits
• VBA Conditional Formatting of Charts by Series Name
• VBA Conditional Formatting of Charts by Category Label
• How do you display a lopsided distribution?