Australian Bureau of Statistics

Rate the ABS website
ABS Home
ABS @ Facebook ABS @ Twitter ABS RSS ABS Email notification service
Education Services
 
 


Education Services home page > CensusAtSchool home page > Year 10 Resources

Resources

Back to Education Services home page

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



    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
    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 1box 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 2all x labels remain
    min
    6
    all y values need to
    and 3 and so onthe 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.



    Downloads


    Student Worksheet









Commonwealth of Australia 2008

Unless otherwise noted, content on this website is licensed under a Creative Commons Attribution 2.5 Australia Licence together with any terms, conditions and exclusions as set out in the website Copyright notice. For permission to do anything beyond the scope of this licence and copyright terms contact us.