# Australian Bureau of Statistics

 ABS Home #search{vertical-align:-3.5px; }
 Education Services

 MAT 13 – Creating Parallel Box and Whisker Plots in Excel

You can download this activity as a Word file using the links at the bottom of the page

Subject Area

Mathematics

Suggested Level

Year 10

Overview

This document details how to use the scatter plot feature of Excel to create box and whisker plots, both singly and in parallel. By calculating the 5 figure summary for a set of data, the minimum, quartile 1, median, quartile 3 and maximum values can be pasted into a template to create the plots.

Requirements

• Excel

Instructions

Parallel box and whisker plots can be drawn in Excel once the 5 figure summary statistics have been calculated. This can be done by hand or using formulae in Excel.

To calculate 5 figure summary statistics in Excel.

Step 1. Copy and paste your data set into an Excel
worksheet.

Step 2. Next to your data set up a table in which to

 minimum Q1 median Q3 maximum

Step 3. In the cell next to minimum type =min(
Select your data, or enter the range of cells e.g. (B2:B20) and Enter

Step 4. Repeat for median and maximum by typing =median( and =max(
respectively into the appropriate cells.

To create a box and whisker plot using Excel
A box and whisker can be created by using the scatterplot feature in Excel. Scatterplots with lines will connect adjacent coordinate pairs with a straight line. Where a space is left between pairs, the line will not connect.

Table 1 below sets out the principles behind and template for a box and whisker plot.

Step 1. Copy the table into your existing Excel worksheet or create a new one.

Step 2. In the 3rd column replace ‘min, Q1, med, Q3 and max’ with their calculated values.

Step 3. To draw a single plot, select the data in the x and y
columns only. You should have 9 pairs. This will
enable the plot to be drawn with 5 vertical and 4
horizontal lines.

Step 4. Go to Insert and select ‘Scatter with Straight Lines’.

Step 5. Remove Vertical axis in the Layout tab under axis.

 box and whisker plot 1 x y Vertical bars 1 min top min 3 min bottom min 1 2 Q1 top Q1 3 Q1 bottom Q1 1 3 med top med 3 med bottom med 1 4 Q3 top Q3 3 Q3 bottom Q3 1 5 max top max 3 max bottom max 1 Horizontal bars 1 box top left Q1 3 box top right Q3 3 2 box bottom left Q1 1 box bottom right Q3 1 3 left whisker minimum min 2 left whisker maximum Q1 2 4 right whisker minimum Q3 2 right whisker maximum max 2 end of plot 1 box and whisker plots 2 all x labels remain min 6 all y values need to and 3 and so on the same as for plot1 min 4 be 3 more for plot 2, 6 more for plot 3 and so on etc. etc.
Table 1: Box and whisker plot template

To create parallel box and whisker plots
Parallel box and whisker plots can be created progressively above the first box and whisker plot by adding to the template in Table 1.

Step 1. Leave one empty row. Copy and paste the single plot template paste it below the
last coordinate pair.

Step 2. Replace the labels in column 3 with the 5 figure summary statistics for the second
data set. Do not change the first data set.

Step 3. In column 4 input a value 3 more than the y value for the first box and whisker plot.

Step 4. Now select all x and y data and insert scatter plot with lines once more.

Step 5. To add more box and whisker plots repeat the last three steps.

Activity

To provide any feedback regarding this activity, please contact ABS Education Services on 1800 623 273 or email education@abs.gov.au.

Back to Mathematics Classroom Activities