Making Box and Whisker Plots in Excel

By Deb Watson (Jan. 2014)

See: http://blog.contextures.com/archives/2010/09/29/airport-security-times-in-excel-box-plot/

1. Arrange the data in your excel spreadsheet as follows:

1. You can either calculate the figures from your data directly or copy and paste from STATISTICA (or from Descriptive Statistics from a similar program)
   1. Average: Mean of your data set
   2. Min: the minimum value observed in your data set
   3. Q1: Lower Quartile
   4. Median: Median
   5. Q3: Upper Quartile
   6. Max: the maximum value in your data set
2. Calculate the following difference formulas beneath your data table
3. The values returned are:

1. Highlight the 25^th Pct, 50^th Pct, and 75^th Pct rows (Row 10-12 from Column B-H).  Then click Insert… Column… Stacked Column (the 2^nd option under 2-D Column).  Watch the following Youtube video that describes how to make these graphs: http://www.youtube.com/watch?v=s8ZW4PVarwE?fs=1&hl=en_US&border=1

1. Excel will generate something that looks like this (see below).  With some editing, we can now create a Box and Whisker plot.

1. Click the upper bar (75^th Pct, green segment) once so that all of the groups are selected.  [If you click twice, it will only select one group.]  Then click, Chart Tools… Layout… Error Bars… More Error Bar Options

1. Under ‘Display’, click the radio button for ‘Plus’ and ‘Cap’, then under ‘Error Amount’ click the radio button for ‘Custom’.  Then Click, ‘Specify Value’.  At the Positive Error Value only (do not put anything in Negative Error Value), click the symbol () and then highlight the ‘Max’ row (in this example it is Row 14) in your difference formula table that is beneath your data set.

1. Click OK, then Close.  Your graph should resemble the following:

1. Now click on the lowest bar (25^th Pct, blue segment) and click, Chart Tools… Layout… Error Bars… More Error Bar Options.  Under ‘Display’, click the radio button for ‘Minus’ and ‘Cap’, then under ‘Error Amount’ click the radio button for ‘Custom’.  Then Click, ‘Specify Value’.  At the Negative Error Value only (do not put anything in Positive Error Value), click the symbol () and then highlight the ‘Min’ row (in this example it is Row 13) in your difference formula table that is beneath your data set.

1. Click OK, then Close.  Your graph should resemble the following:

1. Select the blue 25^th percentile bars again, and under ‘Chart Tools’ click Format… and then expand the ‘Shape Styles’ panel to open the ‘Format Data Series’ dialog.  Click Fill on the left and click the radio button for ‘No fill’.  Click Close.  Your graph will now look like this:

1. Your box and whisker plot is basically completed.  The steps that follow are purely aesthetic.
2. To replace the x-axis with your data labels:  Click the axis, right click and choose ‘Select Data’ and click ‘Edit’ under “Horizontal (Category) Axis Labels’.

1. Next click the symbol () and then highlight the top row (in this example it is Row 2) in your data set.  Click OK, OK.  Your graph will look like this now:

1. I like to delete the Legend and format the y-axis for 1 decimal place.  Your graph will now look like this:

1. Finally, I chose to make the upper and lower quartile bars the same color, added a border (1 pt) around each set of bars in black, and deleted the border around the graph.  If you elect to change each bar individually, you will need to click the segment twice to select the group individually.  I also prefer to delete the gridlines.  You can also change the font style and size, I tend to prefer Arial 10.  I also make the axis lines black (the default is gray) and at least 1 pt thickness.   You can adjust the gap between bars in the Format Data Series dialog.  And add a title to the Y-axis.  Your graph will resemble this now:

1. I like to add a marker for the mean as a large black diamond.  To do this, right click in the white space of the graph and click, Select Data.  In the ‘Select Data Source’ dialog, select ‘Add’ under Legend Entries (Series) on the Left. 

1. In the ‘Edit Series’ dialog, slick the symbol () next to ‘Series values:’ and highlight the ‘Average’ row (in this example it is Row 3) in your data set.  Type “Average’ under the ‘Series name:’.  Click OK, OK.  Your graph will look like this now:

1. It will automatically add the new data as yet another stacked column, but we want a symbol.  To chance this, left click the purple bars to select all 7 bars and under Chart Tools… Design… Click ‘Change Chart Type’ on the far left.

1. Under ‘Line’ click the 4^th option, ‘Line with Markers’.  Click Okay.  Your graph will now resemble the following:

1. All that remains is formatting this line correctly.  First, right click any purple line between the bars and select, Format Data Series.  Under the ‘Format Data Series’ dialog, click ‘Marker Options’ on the Left and then select the radio button for ‘Built-in’ and select the diamond shape from ‘Type’.  Under ‘Marker Fill’, I typically select black from under ‘Solid Fill’. Under ‘Line Color’, select ‘No line’.  Under ‘Marker Line Color’, select ‘No line’.  Close.  Your figure should look like this:

1. Congrats!  Your box and whisker plot with means is now finished!