Excel
Next: Index
Box Plots

Excel can be persuaded to produce a simulation of a box-and-whisker diagram...

Example

Produce a box-and-whisker diagram from this leaf size data:

Leaf LQ UQ Min Max Median
Hawthorn 4.9 6.7 2.1 8.6 5.3
Rose 2.8 4.3 2.2 5.2 3.2
Juniper 4.1 4.9 3.8 6.7 4.5

Step 1

Enter the data in Excel. Note that the interquartile range (UQ-LQ) is needed and that the columns should be in the order given.

data in excel

Select the data as shown and start the Chart Wizard.

Step 2

Select 'Column' as the chart type and 'Stacked Column' as the sub-type. Click 'Next' and select 'Series as column'. Click 'Finish' and admire your chart.

basic stacked bar chart

Step 3

We need to get rid of the first part of each column to leave the floating boxes. Double click on the first part of a column and change the border to 'None' and the area to 'None'. A bit more tweaking leaves us with the boxes:

floating bars

Step 4

Now for the median lines...

We need to add another data series for the median. Go to Chart... Add Data...and select the range G2:G4. The new series should appear as a stacked bar. We need to change this to a line.

Click on the new series so it is selected then go to Chart...Chart Type...and select 'Line' and 'Line with markers displayed'. The medians should appear along a line.

line with markers

Now we need to get rid of the line. Double click on one of the markers. In the 'Patterns' tab set the line to 'None' and the marker to 'Custom', then set the Foreground to black and the Background to 'No colour'. Finally set the Style to the cross. Adjust the Size to about 20.

boxes with crosses

Step 5

For the whiskers we need to add 'error bars'. Double click on one of the crosses. On the 'Y Error Bars' tab select 'Display Both'. For the Error Amount select 'Custom'. Select I2:I4 as the range for '+' and H2:H4 for '-'. Finally we have our whiskers.

After some more tweaking we have our chart:

final box plot
Top of page
Next: Index

  Original content © 2001 D.Keith

Valid XHTML 1.0!