Skip to main content
Skip to main content

Seeing the wood
for the trees

Years 9-10

Using sample study habit data, students summarise data using advanced filtering and grouping techniques, for example pivot tables in spreadsheets and aggregation functions in databases.



Learning hook

  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.
    Image is a screen grab from the Microsoft Excel toolbar ribbon and shows the Sort and Filter icons
    • Triangular arrows will appear above the headings on the data as shown here.
      Image shows the data in the excel table with the top row highlighted with drop down arrows in each cell.
  3. 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.
      Image is of the date parameters. This box displays the various ways the data might be displayed.
    3. The result of the query appears as a filtered subset of the original data set (German, History and Computing):
      The image displays a filtered subset from the original data listing based on dates. It also displays multiple subject that were studied.
    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.
    Image is a screen grab from the Microsoft Excel toolbar ribbon and shows the Sort and Filter icons
    • Triangular arrows will appear above the headings on the data as shown here.
      Image shows the data in the excel table with the top row highlighted with drop down arrows in each cell.
  3. 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).

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

  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:
      Image displays the excel table with data filtered by selecting Saturday and Friday and Computing and Science.
    2. Select Advanced Filter in the Filter drop-down as shown:
      Image displays the filter drop down menu, where Advanced Filter is to be selected.
    3. Both the dataset range and the criteria ranges must be set in the window which appears, as shown:
      The image displays the advanced filter window where the dataset and criteria ranges are set.
    4. Select OK. Five entries will appear as the answer to the query, as shown:
      Study record

Learning construction

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.
    Image displays the excel table with data reflecting the first student's activity as an extension to determine how many occasions this student studied Computing on a Saturday or Science on a Friday for more than 2.5 hours.

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

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

  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.