Boxplots: Creating a Spear range bar chart in Excel

This article shows ways to produce a range chart - an early version of box plot - using Excel. There are different ways to produce box plots on Excel. Microsoft eventually added a box plot to the standard chart gallery in Excel 2016. The methods set out here work for earlier versions of Excel as well

Mary Eleanor Spear illustrates  the range bar in her 1952 book 'Charting Statistics' 

The essentials of this chart are that it uses the five number summary of the spread of the data, with the inter-quartile range (IQR) as a box and the range beyond the IQR as whiskers. These are all the essential elements of a box and whisker plot, 25 years earlier than John Tukey's 1977 book Exploratory Data Analysis which many people herald as the point at which the box plot was 'invented'. I'm calling this the 'Spear range bar' in an attempt to restore some of the credit to her.

We need some example data to demonstrate the method in Excel

These are the key five numbers. First step, we need to calculate the spans between each. This includes the span from zero to the minimum (which  will serve to 'pad' the left hand side of the chart)

Next use the calculated span data to create a stacked bar chart

Excel may default to setting up the bars the 'wrong' way round (as above). If so you will have to twist its arm using 'Select Data' then 'Switch Row/Column'

You may also want to restore the sequence (Format Axis ...Categories in Reverse order)

 





















Now we have got all the bits in the right places, most of the rest is formatting.

We need to 'get rid' of' the 0 to Min span - it is just the padding needed to push the edge of the range away from zero. So reformat that Series so that there is no fill and no border line.

Next reformat the two parts of the interquartile range (Q1 to Median and Median to Q3) as simple boxes (no fill, solid line border)



Next we need to format the whiskers  (Min to Q1 and Q3 to Max). A quick way of doing this is to use a picture fill. In a spare corner of the spreadsheet, set up a thick single cell border. Then take a picture of it (the Windows Snipping Tool works well).

Then use this picture as the fill for the series....Insert from Clipboard. You will probably also want to format to 'Stack' (to ensure the line thickness stays the same irrespective of the whisker length

























Format to no border line. Repeat for the other whisker and we are nearly finished

Format to taste:

It' a little clunky but it's fast and effective

If you look on Google for how to create boxplots in Excel, you will find many blog articles all advising the same trick: namely customising error bars to create the whiskers

This works well but takes a little more setting up than the picture fill method outlined above.

The left hand whisker is created by adding an error bar to the Min to Q1 series. Select the Minus only option. For the Error Amount pick Custom and then enter the range for the  Min to Q1 series.data



































To create the right whisker, an an error bar to the Median to Q3 series (not the Q3 to Max). Set to Plus to get the bar to extend to the right

To reflect the Spear chart style better, the No Cap option can be selected for End Style. Adjusting to this and thickening up the error bar line slightly gives:
















References - a selection of 'how tos'

http://www.dummies.com/education/math/statistics/box-and-whisker-charts-for-excel/

http://www.contextures.com/excelboxplotchart.html

https://nathanbrixius.wordpress.com/2014/03/10/beautiful-box-plots-in-excel-2013/

https://peltiertech.com/excel-box-and-whisker-diagrams-box-plots/

https://www.vertex42.com/ExcelTemplates/box-whisker-plot.html