Be able to select subsets of data using a single criterion, or multiple criteria

Spreadsheets – Selecting Subsets of Data (Cambridge IGCSE ICT 0417)

Learning Objectives

By the end of this lesson students will be able to:

• Design a well‑structured data model and use named ranges and structured table references.
• Apply a full range of basic and intermediate functions required by the syllabus (SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, ROUND, IF, IFERROR, TEXT, DATE, AND, OR, VLOOKUP/XLOOKUP, nested IF).
• Sort, search and filter data using:
  • AutoFilter (single‑criterion)
  • Advanced Filter (multiple‑criterion – AND / OR)
  • Formula‑based helper columns or the FILTER function.
• Use Conditional Formatting to highlight records.
• Create and customise a simple chart to present the filtered data.
• Interpret the results (AO3 – analysis & evaluation).

1. Setting Up a Proper Data Model

• Field headings – a single row of concise, non‑blank headings; avoid merged cells.
• Data types – decide whether each column holds text, numbers, dates or currency and keep the format consistent.
• Define a table – select the range and choose Insert → Table. The table automatically receives a name (e.g. SalesTbl) and structured references such as SalesTbl[Units Sold].
• Named ranges – useful for formulas that refer to the whole dataset.

  Formulas → Define Name → Name: SalesData → Refers to: =SalesTbl

• Absolute vs. relative references – lock rows/columns with $ when copying formulas.
  • A2 – relative.
  • $A2 – column locked.
  • A$2 – row locked.
  • $A$2 – both locked.

2. Sample Data Set

3. Essential Functions (Syllabus 20.1)

4. Sorting, Searching and Custom Sort (Syllabus 20.2)

• Simple sort – Data → Sort.
  • Primary: Region (A‑Z).
  • Secondary: Total Sales (£) (Largest → Smallest).
• Custom sort – useful for non‑alphabetical orders (e.g., colour or priority).
  1. Create a helper column called SortOrder with numbers that reflect the required order (e.g., North = 1, South = 2, East = 3, West = 4).
  2. Sort by SortOrder instead of the text column.
• Search
  • Keyboard shortcut Ctrl + F for a quick find.
  • Formula‑based search: =SEARCH("Elect",[@Category]) returns the position of “Elect” – helpful for partial matches.

5. Selecting Subsets of Data

5.1 Single‑Criterion Filtering – AutoFilter

1. Select any cell inside the table.
2. Go to Data → Filter → AutoFilter. Drop‑down arrows appear in the header row.
3. Click the arrow for the required column and choose a condition (e.g., Category = Electronics or Region = North).
4. The sheet now displays only rows that meet that single criterion.

5.2 Multiple‑Criterion Filtering – Advanced Filter

Advanced Filter can handle **AND** and **OR** logic across several columns.

1. Leave at least one blank row below the table (or use a separate area on the same sheet).
2. Copy the exact header names you will use as criteria. Example for “Electronics items with sales > £1 500”:

   Category          Total Sales (£)
   Electronics       >1500
           

3. To apply **OR** logic, place each alternative condition on a separate row:

   Category          Total Sales (£)
   Electronics       >1500
   Clothing          <600
           

   This returns rows that are either Electronics > 1500 **or** Clothing < 600.
4. Select the original table, then choose Data → Advanced Filter.
5. In the dialog box set:
   • List range: =SalesTbl (or $A$2:$F$9).
   • Criteria range: the cells you just created.
   • Choose “Filter the list, in‑place” (or “Copy to another location”).
6. Click OK. Rows that satisfy the combined criteria are shown.

5.3 Formula‑Based Helper Column

1. Add a new column to the right of the table and label it Match?.
2. Enter a logical formula that combines the required tests. Example – “Electronics items with sales > £1 500”:

   =AND([@Category]="Electronics",[@[Total Sales £]]>1500)

3. Copy the formula down the column.
4. Apply an AutoFilter to the Match? column and select only TRUE.

5.4 Using the FILTER Function (Excel 365/Google Sheets)

If the exam board permits dynamic arrays, the FILTER function provides a single‑formula solution.

=FILTER(SalesTbl, (SalesTbl[Category]="Electronics")*(SalesTbl[Total Sales £]>1500))

The result is a new spill range containing only the rows that meet both conditions.

6. Conditional Formatting – Visual Selection (Syllabus 20.2)

1. Select the whole table (A2:G9).
2. Home → Conditional Formatting → New Rule → “Use a formula to determine which cells to format”.
3. Enter a formula that reflects the desired condition, e.g.:

   =$E2>2000

4. Choose a fill colour (light orange works well) and click OK.

All rows where Total Sales (£) exceeds £2 000 are now highlighted, giving an instant visual cue of high‑performing items.

7. Presenting Data – Simple Chart (Syllabus 20.3)

1. Apply the required filter (e.g., show only “Electronics”).
2. Select the visible columns Item ID and Total Sales (£) (or the whole filtered range).
3. Insert → Chart → Column Chart.
4. Customise the chart:
   • Chart Title: “Electronics – Total Sales by Item”.
   • Axes: Set the vertical axis minimum to 0 and maximum to a round number just above the highest sales value (e.g., 3 200).
   • Data Labels: Add to show exact figures.
   • If you also want to display Units Sold, add a secondary vertical axis and change that series to “Line”.
   • Use the Design tab to apply a clear colour scheme that matches any Conditional Formatting used.

8. Practical Activities (Exam‑style Tasks)

1. AutoFilter: Show only rows where Region is “South”. Record the number of visible rows.
2. Advanced Filter (AND): Display all Clothing items with Total Sales (£) less than £600.
3. Advanced Filter (OR): Show rows that are either Electronics with sales > £1 500 **or** Furniture with units sold ≥ 5.
4. Helper Column: Create a Match? column that returns TRUE when Units Sold > 10 and Unit Price (£) < 100. Filter on this column.
5. Conditional Formatting: Highlight rows where Total Sales (£) exceeds £2 000. Explain how this visual cue helps a manager.
6. Chart Creation: Using the filtered “Electronics” data, produce a column chart with data labels and a secondary line series for “Units Sold”.
7. Analysis (AO3): Write a short paragraph (3‑4 sentences) interpreting the filtered results – e.g., which region generates the most revenue, whether high‑price items compensate for lower units sold, etc.

9. Tips for Exam Success (Syllabus 20.3)

• Exact header text: The criteria range must contain the header **exactly** as it appears in the data table – no extra spaces, no different capitalisation.
• Absolute references: Use $ only where you intend the reference to stay fixed when copying a formula across rows or columns.
• AutoFilter vs. Advanced Filter: AutoFilter handles one condition per column; Advanced Filter can handle multiple columns and both AND & OR logic.
• OR logic in Advanced Filter: Place each alternative condition on a separate row within the criteria range.
• Custom sort: Remember to create a numeric helper column if you need to sort by a non‑alphabetical order.
• Clear filters before new tasks: Data → Clear to avoid hidden rows affecting subsequent calculations.
• Structured references: When the exam allows, use table names (e.g., SalesTbl[Units Sold]) – they are shorter and automatically adjust when rows are added or removed.
• Check for errors: Wrap look‑up formulas in IFERROR to avoid #N/A messages that can cost marks.