Skip to main content
Skip to main content

A spreadsheet's secret weapon

Years 9-10

Students will learn to use pivot tables which have been described as the most powerful tool within spreadsheets.

Learning hook

  1. Provide all students with the spreadsheet file: Pivot tables and advanced filtering (XLSX).
    • Have students open the worksheet tab labelled Data 1.
  2. Give students 10 minutes to try to complete the task of identifying how many episodes each actor was in and what their total earnings were, without using pivot tables.
    (Note: students will probably find this tedious. This is the intention! The hook for this lesson is a negative one.)
  3. Point out to students how tedious this process was.

Learning map and outcomes

Despite their ready availability, many students have little sophisticated understanding of spreadsheets. Spreadsheets are used in this lesson to analyse data and to produce information using a relatively little known but remarkably powerful tool: pivot tables. You may wish to discuss the analytical mindset which would be of use to learners in this learning sequence.

Learning input

Tell students: 

  • You probably found this process frustrating and boring. You were also likely to easily make errors.

Ask them to have a look at the next tab on the sheet: Pivot tables and advanced filtering (XLSX) > Sheet: Pivot table 1A

  1. Ask the original question from the Learning hook once more:
    • How many episodes was each actor was in and what was their total earnings?

    Can they see how easy it appears now?

  2. Using the sheet, Data 1, demonstrate the creation of Pivot table 1A.  
    • a. Select all the data, including the title and column headings, A1:F31
    • b. Select the Data tab and then Click the Pivot table arrow beside the Pivot table icon in the ribbon

    Data > Create Manual Pivot table 

    (If students click Automatic Pivot table by mistake, they can drag the entries out of the boxes and it will leave all fields empty.)

    Analysis menu

    PivotTable Builder menu

    Note: if this Pivot Builder window disappears, it can be switched back on from the ribbon Data > View > Builder

    View menu

    c. In Field names select checkboxes for Surname, Episode and Earnings.

    d. Change Values drop downs to read:

    • Sum of Earnings and
    • Count of episodes

    as shown below:

      PivotTable Builder menu

      The result appears immediately as a new worksheet.

  3. Point out that Grand Totals are also generated automatically as shown below:
  4. Example of a pivot table

  5. Ask for student feedback about their impressions of the potential of this tool.

Learning construction

Set students the task of reproducing the remaining pivot tables provided as examples in the spreadsheet file.

  1. Ask students to recreate the following two pivot tables:
  2. Next direct students to Data set 2:
  3. They may be familiar with this data set from a previous lesson sequence.

    • Students’ challenge here is to create a pivot table showing each Subject and a summary of the Completed column (‘Yes’ or ‘No’) which indicates how often study was finished in the time available.
  4. Students’ next challenge is to create a pivot table showing:
  • Subject
  • Count and Type of each session
  • a summary of the Completed column (‘Yes’ or ‘No’) which indicates how often study was finished in the time available.

Solutions to these are provided in the files: Pivot tables and advanced filtering (XLSX) > Sheet: Pivot table 2A and Pivot tables and advanced filtering (XLSX) > Sheet: Pivot table 2B

(These two sheets may be removed before providing the file to students.)

Learning demo

  1. Provide, or develop with students, datasets which are relevant to their interests and activities.
  2. These are freely available as .csv files. Select the Datasets tab at top of page.

  3. Students develop their own data sets or use one downloaded from Australian Government:
  • Have them demonstrate a useful application of pivot tables for their data.
  • They must be able to state what the criteria are for which their pivot table is the solution.

Note: check students’ proposed data sets for suitability before they commence.