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.

Item ID	Category	Units Sold	Unit Price (£)	Total Sales (£)	Region
101	Electronics	15	120	1800	North
102	Clothing	30	25	750	South
103	Electronics	8	200	1600	East
104	Furniture	5	350	1750	West
105	Clothing	12	30	360	North
106	Electronics	20	150	3000	South
107	Furniture	3	400	1200	East
108	Clothing	25	22	550	West

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

Function	Purpose	Example Syntax	Result (for row 2 of sample data)
IF	Return one value if a condition is true, another if false.	$$\text{=IF}(C2>10,"High","Low")$$	“High” (because Units Sold = 15 > 10)
AND	True only if all arguments are true.	$$\text{=AND}(B2="Electronics",E2>1500)$$	TRUE (Category = Electronics and Total Sales = 1800 > 1500)
OR	True if any argument is true.	$$\text{=OR}(D2<30, F2="South")$$	FALSE (Unit Price = 120 > 30 and Region = North)
NOT	Reverses the logical value.	$$\text{=NOT}(B2="Clothing")$$	TRUE (Category is not Clothing)

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.