Seeing the wood for the trees

1. Provide students with a set of data recording a year 11 student’s study habits over about 4 months: Study data (XLS)
2. 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:

1. Select any cell inside the range of the data set. 
2. 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.

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

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:

1. Select any cell inside the range of the dataset.
2. On the toolbar choose Data > Sort & Filter then select the Filter icon.
   
3. Triangular arrows will appear above the headings on the data as shown here.
   
4. Select the arrow above Day and select Saturday. The label will change from a triangle to the Filter icon.
   
   
   1. 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.
   2. The time in hours will be converted to 0.0833333333333333 (which is the fraction 2 hours is of one day).

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.

1. 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.
   
   
2. Let us suppose our query is: How many occasions did this student study Computing on a Saturday OR Science on a Friday?
   
   
   1. Have students enter ‘Saturday’ and ‘Friday’ in cells B3, B4 respectively and ‘Computing’ and ‘Science’ in cells C3, C4 respectively, as shown:
      
   2. Select Advanced Filter in the Filter drop-down as shown:
      
   3. Both the dataset range and the criteria ranges must be set in the window which appears, as shown:
      
   4. Select OK. Five entries will appear as the answer to the query, as shown:

Introduces more complex ‘OR’ filtering:

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

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

1. 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
2. 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.