20.2 Manipulate Data

2026 Syllabus Objectives

  • Sort data: Using a single criterion, or multiple criteria sort data into ascending or descending order
  • Search and select data: Using a single criterion, or multiple criteria, select subsets of data
  • Perform searches using a variety of operators including AND, OR, NOT, >, <, =, >=, <=, <>
  • Perform searches using wildcards

Filtering Data 🔍

https://cambridgeassistant.com

Filtering allows you to display only the rows that meet specific criteria while hiding the rest. This is accomplished using the AutoFilter feature in spreadsheet software.

Setting Up AutoFilter

To enable filtering on your data:

  1. Highlight the data range (e.g., cells A2 to C24)
  2. Select the Data tab in the ribbon
  3. Click the Filter icon in the Sort & Filter section
  4. Drop-down arrows appear in the top right corner of each column header

Basic Filtering with Single Criteria

Once AutoFilter is enabled, you can filter data by clicking the drop-down arrow on any column:

  • Click the arrow on the column you want to filter
  • Uncheck '(Select All)' to deselect everything
  • Check only the values you want to display
  • Click OK

Example: To show only employees working on the 'Binnaccount' task:

  • Click the CurrentTask column arrow
  • Uncheck '(Select All)'
  • Check only 'Binnaccount'
  • Click OK

🔑 Key Term: AutoFilter is a feature in spreadsheet software used to display only the rows that meet specific criteria while hiding the rest.

Searching Using Number Filters 🔢

https://cambridgeassistant.com

Number filters provide more sophisticated search capabilities than simple checkbox selection. They allow you to use logical operators to define ranges and conditions.

Using Custom Number Filters

To access number filters:

  1. Click the drop-down arrow on a numeric column
  2. Select 'Number Filters'
  3. Choose 'Custom Filter...'
  4. Define your criteria using logical operators

Logical Operators for Number Filters

The following operators are available:

OperatorSymbolMeaning
Equals=Exactly equal to
Does not equal<>Not equal to
Greater than>Strictly greater
Less than<Strictly less
Greater than or equal to>=Greater or equal
Less than or equal to<=Less or equal

Range Searches with AND Logic

To find values within a range, use the AND operator to combine two conditions.

Example: Select employees where the task code is between 3 and 6 inclusive.

The mathematical logic is:

x≥3 AND x≤6x \geq 3 \text{ AND } x \leq 6

Steps:

  1. Click the arrow on 'TaskCode'
  2. Select 'Number Filters' > 'Custom Filter...'
  3. Set the first condition: 'is greater than or equal to' and enter 3
  4. Select the 'And' radio button
  5. Set the second condition: 'is less than or equal to' and enter 6
  6. Click OK

Sign in to view full notes