Education Services home page > Mathematics
 |
MAT 13 – Creating Parallel Box and Whisker Plots in Excel |
|
|
|
|
|
|
|
|
|
|
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.
|
|
|
|
|
|
|
|
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
record your results.
| 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.
|
|
|
|
|
|
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
|
This page last updated 11 December 2012 | 
