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

20 Spreadsheets – Selecting Subsets of Data

Learning Objective

Students will be able to select subsets of data in a spreadsheet using a single criterion or multiple criteria.

Key Concepts

• Understanding what a criterion is – a condition that data must meet to be included.
• Using the AutoFilter for quick, single‑criterion filtering.
• Applying Advanced Filter or formula‑based methods for multiple criteria.
• Common logical functions: IF, AND, OR, NOT.
• Using Conditional Formatting to highlight rows that meet criteria.

Sample Data Set

The table below represents a simple sales register. It will be used for all examples.

1. Selecting Data Using a Single Criterion (AutoFilter)

1. Select any cell within the data range.
2. Activate the AutoFilter:

   • In most spreadsheet programs, choose Data → Filter → AutoFilter.

3. Drop‑down arrows appear in the header row. Click the arrow for the column you wish to filter.
4. Choose a single condition, for example:

   • Show only rows where Category = Electronics.
   • Show only rows where Region = North.

5. The spreadsheet now displays only the rows that meet the chosen criterion.

2. Selecting Data Using Multiple Criteria (Advanced Filter)

When more than one condition is required, the Advanced Filter (or a formula‑based approach) is used.

2.1 Setting Up a Criteria Range

1. Leave at least one blank row below the data table.
2. Copy the header names of the columns you will use as criteria (e.g., Category and Total Sales (£)) into a new area.
3. Enter the conditions directly beneath each header. Use logical operators (>, <=, etc.) and wildcards if needed.

   • Example criteria for “Electronics” items with sales over £1,500:

     Category          Total Sales (£)
     Electronics       >1500
     

4. Select the original data range, then choose Data → Advanced Filter.
5. In the dialog box:

   • Set “List range” to the original table.
   • Set “Criteria range” to the cells you just created.
   • Choose “Filter the list, in‑place” or “Copy to another location”.

6. Click OK. The rows that satisfy both conditions are displayed.

2.2 Using Formula‑Based Criteria

Formulas can be placed in a helper column to flag rows that meet multiple conditions.

1. Add a new column called Match? to the right of the table.
2. Enter a formula that combines logical tests. For the same example as above:

   \$\text{=AND}(B2="Electronics",E2>1500)\$

   • Here, B2 refers to the Category cell and E2 to Total Sales (£).
   • The formula returns TRUE if both conditions are satisfied, otherwise FALSE.

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

3. Common Logical Functions

4. Practical Activities

1. Single‑criterion filter: Using the sample data, filter to show only rows where Region is “South”. Record the number of rows displayed.
2. Multiple‑criterion filter (Advanced Filter): Show all Clothing items with Total Sales (£) less than £600.
3. Helper column method: Add a Match? column that returns TRUE for rows where Units Sold is greater than 10 and Unit Price (£) is less than 100. Filter on this column.
4. Conditional Formatting: Apply a rule that highlights rows where Total Sales (£) exceeds £2,000. Explain how this visual cue can aid data selection.

5. Tips for Exam Success

• Always ensure the criteria range includes the exact header text; even a stray space will cause the filter to fail.
• When using formulas, lock column references with $ only if you intend to copy the formula across columns.
• Remember that the AutoFilter works on the current view only; the underlying data remains unchanged.
• For multiple criteria that involve “or” logic, list each condition on a separate row within the criteria range.