Filtering lists

We can sort and manage a list of different data in Excel 97, as easily as we do in a database. We consider the rows as records and columns as fields. Each column has a field name, its format will be different from the format of data.

Filtering data from a list is done with the help of the automatic filter.

• Select any cell in the list
• Choose Filter from the Data menu
• Choose Auto filter option (AutoFilter)
• Filter arrows appear next to the column names
• Click on one of the filter arrows beside the column name
• You will see a list of filter criteria

You should select an option from the list of criteria, then all data from the worksheet is filtered, for example you can filter all the staff who work on the after sales service. The row numbers are blue, the filter arrow in the filtered column is blue.

If you want to display all the data again, select Filter ® option the Data menu and select Show all (Show All).

Finding a custom filter:

For example, to filter for all staff who started working in 1991 when the company started

• Click on the filter arrow next to the ‘start date’ column
• Choose ‘custom’ (Custom) in the list
• Choose the correct operator
• In the second box, choose the first date of 1991
• Click Ok

The records are filtered to match the set criteria, choose the Show all (All) to see all records again.

Another option is the top10

• Click on the arrow next to salary
• Choose the option top10
• Again, you can make a choice (Top 10 or Top 5, Bottom 10 or Botton 5, etc.)

To disable the filter, select Filter in the Data menu and select ‘Show All’.
When all the records appear back then choose the filter option from the Data menu and you turn the auto filter on or off by clicking on it.

Complex filters

You will need to establish the criteria in your worksheet.

• First add some new rows at the top of your spreadsheet
• Turn off the title block (see previous lesson)
• Select the column names
• and drag them with the Control key to the first row
• Choose a different background color and text color for the new row of column names to distinguish them
• Select the entire test range (row of column names and an empty row below)
• Click in the box and type a name for example: criteria

For example, if you want to filter the list for the female staff with a salary greater than 450,000:
Type under the salary criterion> 450000
and then in the sex field criterion: F

• Click on a cell somewhere in the list
• Click on the “Filter” ® from ‘Data’ ® menu option and select ‘Advanced filter‘ (Advanced filter)
• A dialog appears in which the list is automatically selected
• Click Copy to another location (Copy to another location)
• In the “criteria range” type: “criteria”
• In the “copy to” enter: L1
• Click OK.

And you’ll see that in cell L1 only those records that meet the selected criteria appear.

More complex criteria

We make a criterion of all female employees with an annual salary> 450,000 and all staff working in the hardware department.

• Decide new criteria fields (department, salary, sex)
• We already have two criteria (salary> 450,000 and gender F)
• Now we make an OR criterion and place it on the next row
• In the cell A3, type ‘hardware’

• Place the cursor anywhere in the list
• Select the’Advanced Filter’ from the ‘Filter’ option in the Data menu
• Enter the criteria range in the box, we fill the range by selecting in the worksheet (A1: C3)
• Choose ‘copy’ to another location and type eg: L20

• Click OK, and the records are filtered.

 

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats