Making a Box and Whisker Plot

Getting the Data Ready

Step 1: Prepare the Data for the Chart. Insert several blank rows to set up range for calculations. This is represented by cells A16 – G24 in the example above.

Step 2: Calculate the data needed for construction of the box plots.

Step 3: Copy these calculations to columns C through G. Cells C17 through G24 in the example above.

Step 4: Determine the values to plot. This is represented by cells A27 through G31. In the above example.

A: The bottom of the lower box rests on the first quartile.

B: Each box plot in the chart above has a box for the second quartile, which shows the difference between the median and first quartile calculated in the “Calculations” table above.

C: Each box plot also has a box for the third quartile, which shows the difference between the third quartile and the median calculated in the “Calculation” table above.

D: The down whisker is as long as the first quartile minus the minimum value

E: The up whisker is as long as the maximum value minus the third quartile.

Step 5: Copy these calculations to columns C through G. Cells C27 through G31 in the example above.

Now you are ready to start building the chart.

Making the Chart

Step 1: Create your Chart. Highlight cells associated with Bottom, 2Q, 3Q (cells A27 through G29 in the example above) and click “Insert” tab above. In the “Charts” menu click “Column” and select “2D Stacked Column Chart”.

Step 2: Add the Down Whisker. Select the bottom series (blue boxes), click “Chart Tools” and click “Layout Tab”. Click “Error Bars” and select “More Error Bar Options” from the bottom of the menu. Under the “Vertical Error Bars” section select the “Minus” direction, under the “Error Amount” section select “Custom” and click “Specify Value”. In the pop up box leave the “Positive Error Value” box alone. In the “Negative Error Value” box clear the error value and click on the spreadsheet icon in the right hand side of the box and highlight cells B30 through G30 for the “Whisker -” values. Click the spreadsheet icon in the right hand side of the box. Click “OK”. Click “Close”.

Step 3: Add the Up Whisker. Select the 3Q box series (the green boxes) and click “Chart Tools”. Click “Layout Tab” and click “Error Bars”. Select “More Error Bar Options” from the bottom of the menu. Under the Vertical error Bars section select the “Plus” direction, under the “Error Amount” section select “Custom” and click “Specify Value”. In the pop up box leave the “Negative Error Value” box alone. In the “Positive Error Value” box clear the error value. Click the spreadsheet icon in the right hand side of the box and highlight cells B31 through G31 for the “Whisker +” values. Click the spreadsheet icon in the right hand side of the box. Click “OK”. Click “Close”.

Step 4: Format the Boxes. Select the bottom series (blue boxes), right click and select “Format Data Series”. Under “Fill” select “No fill” and under “Border Color” select “No Line”. Click “Close”.

Step 5: Format the Boxes. Select the bottom series (red box) and right click. Select “Format Data Series”. Under “Fill” select solid fill and change to a light blue. Click “Close”. Repeat this step for the green box and select the same color.

Step 6: Add the Mean. To add the mean as a series of markers on the box, click the chart and click “Chart Tools” above. Click “Select Data”. In the pop up box under the “Legend Entries” section click “Add”. In the pop up box under “Series Name” click the spreadsheet icon on the right hand side of the box and highlight cell A18 (Mean) then click the spreadsheet icon. Under “Series Values” section click on the spreadsheet icon on the right hand side of the box and highlight cells B18 – G18. Click the spreadsheet icon. Click “OK”, then click “OK”.

Step 7: Format the Mean Data Series. Select the purple boxes, click “Chart Tools”, click “Change Chart Type” and select a Line chart using the first icon under the Line option. Click “OK”.

Step 8: Reformat the Line. Click the line you just made and right click. Click “Format Data Series” and in the pop up box under “Marker Options” select “Built in” and change to a diamond (in this example). Under “Line Color” select “No Line”. Click “Close”.

Step 9: Add Data labels to the Horizontal Axis. Click the horizontal axis and right click. Select “Select Data” under the “Horizontal Axis Labels” section of the pop up box and click “Edit”. In the pop up box click the spreadsheet icon on the right hand side of the box and highlight cells B16 – G16 above. Click the spreadsheet icon. Click “OK”. Right click the labels, select “Font” and change the font size to 8 (as in this example).

Step 10: Delete the Legend. Click the legend and press the delete key (DEL).

Step 11: Add a Title. Click the chart and then click “Chart Tools”. Click the “Layout” tab, click “Chart Title” and select “Above Chart”. Click the title and change text to “Average Number of Emergency Patients Arriving by Time of Day”. Click the chart title, right click and select “Font”. Change the size to 10 (as in this example). To change the vertical axis scale, click the vertical axis and right click. Select “Format Axis” and under “Axis Options” change the “Maximum” to “Fixed” and change “Number” to 35. Change “Major Unit” to “Fixed” and change number to 5. Click “Close”.

Learn more about Box and Whisker plots by checking out my newsletter here.