A spreadsheet's secret weapon

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.

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.

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.)

   

   

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

   

   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:

   

   The result appears immediately as a new worksheet.

3. Point out that Grand Totals are also generated automatically as shown below:

   

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

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:
   • Pivot tables and advanced filtering > Sheet: Pivot table 1C
   • Pivot tables and advanced filtering (XLSX) > Sheet: Pivot table 1B
2. Next direct students to Data set 2:
   • Pivot tables and advanced filtering (XLSX) > Sheet: Data 2

   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.
3. 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.)

1. Provide, or develop with students, datasets which are relevant to their interests and activities.
   • The Australian Government datasets are an excellent student resource for database work: Australian Government: data.gov.au

   These are freely available as .csv files. Select the Datasets tab at top of page.

   • For example, see this database on heritage sites: Commonwealth Heritage List – current
2. Students develop their own data sets or use one downloaded from Australian Government: data.gov.au
• 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.