# Seeing the wood for the trees

## About this lesson

In this lesson sequence students summarise data using advanced filtering and grouping techniques, for example pivot tables in spreadsheets and aggregation functions in databases.

Year band: 9-10

Curriculum Links Assessment### Curriculum Links

Links with Digital Technologies Curriculum Area

Strand | Content Description |
---|---|

Processes and Production Skills |
Analyse and visualise data interactively using a range of software, including spreadsheets and databases, to draw conclusions and make predictions by identifying trends and outliers (AC9TDI10P02). Model and query entities and their relationships using structured data (AC9TDI10P03). |

### Assessment

Note: Criteria are cumulative

Quantity of knowledge |
Quality of understanding |
||||

Basic spreadsheet filtering |
No evidence of understanding | Student is able to find the basic spreadsheet filter tool | Student is able to use a basic spreadsheet filter on given data when given the required field entries | Student is able to use unassisted a basic spreadsheet filter on given data | Student is able to anticipate the results of a basic spreadsheet filter on a spreadsheet |

Advanced spreadsheet filtering |
No evidence of understanding | Student is able to find the spreadsheet filter tool and recognises how additional data is entered for advanced filtering | Student is able to use an advanced spreadsheet filter on given data when given the required field entries | Student is able to use unassisted an advanced spreadsheet filter on given data | Student is able to anticipate the results of an advanced spreadsheet filter on a spreadsheet |

Optional score |
0 | 1 | 2 | 3 | 4 |

### Learning hook

- Provide students with a set of data recording a year 11 student’s study habits over about 4 months: Study data (XLS)
- Explain the data set:

- This student accepted a challenge to record their work over the first months of the year.
- They wish now to analyse how much time they have spent on different subjects.
- Rather than enter complicated formulas for this into their spreadsheet, they decide to use filters.

#### Activity 1: Using the Filter in Excel

Demonstrate to students how to turn on the Text and Number filter in order to answer the query: Which subjects did I study during March?**Method:**

- Select any cell inside the range of the data set.
- On the toolbar (called the ‘ribbon’ by Microsoft) ribbon choose Data > Sort & Filter then select the Filter icon.

- Triangular arrows will appear above the headings on the data as shown here.

- Triangular arrows will appear above the headings on the data as shown here.
- Select the arrow above
*Date*. The label will change from a triangle to the Filter icon.- In the first drop-down menu labelled
*Select**one*, select*All**dates in the period*. (It's the last choice.) - In the next drop-down below this select
*March*. - The result of the query appears as a filtered subset of the original data set (
*German, History and Computing*): - Remind students that the Filter button must be clicked off for each new query. If it is greyed, it is still on.

- In the first drop-down menu labelled

#### Activity 2: Answering queries

Ask students to use the Filter in Excel to answer the query: Which subjects have I spent 2 hours or more studying on Saturdays?**Method:**

- Select any cell inside the range of the dataset.
- On the toolbar choose Data > Sort & Filter then select the Filter icon.

- Triangular arrows will appear above the headings on the data as shown here.

- Triangular arrows will appear above the headings on the data as shown here.
- Select the arrow above
*Day*and select*Saturday*. The label will change from a triangle to the Filter icon.

- Click the arrow above
*Elapsed**time*and select from*Greater than or equal*to from the*Choose one*drop-down menu. Enter 2 hours, using the form ‘2:00:00’ in order to indicate it is in time format. - The time in hours will be converted to 0.0833333333333333 (which is the fraction 2 hours is of one day).

- Click the arrow above

### Learning map and outcomes

Spreadsheets are used in this lesson to analyse data and to produce information using advanced filtering techniques. You may wish to discuss the analytical mindset which would be of use to learners in this learning sequence.

### Learning input

- Explain to students that the standard Filter (as used above in Learning hook) is only useful for
**AND**queries. We need the Advanced Filter tool for**OR**queries.

- For example: we could not use the Filter tool to find all occasions when this student studied Computing on a Saturday OR History on a Friday.
- When using the Advanced Filter we need to enter the criteria for the query on the worksheet itself. A template for this is already provided on the file supplied, with the heading ‘Criteria for advanced filtering’ and shown with a blue border.

Work through the following activity with students. - For example: we could not use the Filter tool to find all occasions when this student studied Computing on a Saturday OR History on a Friday.
- Let us suppose our query is: How many occasions did this student study Computing on a Saturday OR Science on a Friday?

- Have students enter ‘Saturday’ and ‘Friday’ in cells
*B3, B4*respectively and ‘Computing’ and ‘Science’ in cells C3, C4 respectively, as shown: - Select
*Advanced Filter*in the Filter drop-down as shown: - Both the dataset range and the criteria ranges must be set in the window which appears, as shown:
- Select
*OK*. Five entries will appear as the answer to the query, as shown:

- Have students enter ‘Saturday’ and ‘Friday’ in cells

### Learning construction

Introduces more complex ‘*OR*’ filtering:

- Sets students three complex
*OR*queries based on this or another data set and students use the*Advanced Filter*function to extract the correct data.

- For example: Set the first student activity as an extension of the teacher input: How many occasions did this student study Computing on a Saturday OR Science on a Friday for more than 2.5 hours?

(Note: Add the criterion >2:30:00 in F3)

Result: On four occasions. - For example: Set the first student activity as an extension of the teacher input: How many occasions did this student study Computing on a Saturday OR Science on a Friday for more than 2.5 hours?
- Students then devise three more complex ‘OR’ queries based on this or another dataset. They use the Advanced Filter function to extract the correct data subset.

### Learning demo

- Explain that the Australian Government datasets are an excellent student resource for database work. These are freely available as .csv files:

- Australian Government: data.gov.au

Select the*Datasets*tab at top of page - For an example, see this database on Heritage sites

Commonwealth Heritage List – current

- Australian Government: data.gov.au
- Students use a dataset and devise complex queries which they then answer using the Advanced Filter.

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