Excel worksheets can be used to keep lists of information such as employee details, subscribers, accounts, sales, or even bird sightings if need be. The Advanced Filter in Excel enables you to analyse the data in a list by filtering it according to specific criteria. Excel worksheets can be used to keep lists of information such as employee details, subscribers, accounts, sales, or even bird sightings if need be. The Advanced Filter in Excel enables you to analyse the data in a list by filtering it according to specific criteria.
To work with Advanced Filters in Excel, you need to understand a few of the concepts that are used. A list in Excel is a series of rows of information. Each row is effectively one unit of information. This structure is very similar to a simple database and therefore Excel uses similar terminology to describe the parts of the list. The following illustrates a list and its parts.
Fields, Field Names and Records in Excel
A field is a column in the list of data. In the example below, the column of data for the Last Name is an example of a field.
The field name is the heading at the top of the column. The field names within one list must be unique. In the example below, the text Annual Fee is an example of a field name.
A record is a row of data in the list. Each record is one item of data in the list. In the example below, the row of information for Fred Jackson is one record. Note that advanced filters do not work correctly if there are blank rows in the list.
Criteria, Criteria Range, AND and OR in Excel
Criteria are tests against the data in specific fields, for instance Gold. When Gold is tested against the field Type, the filter would display only the people with Gold memberships. All other records are filtered out (hidden).
The criteria range is the area where you specify the criteria. The first row contains the field names that mirror those in the list. The second and subsequent rows are used to type the criteria or examples of what you are looking for in the list.
If you want the conditions between fields joined with an AND, you write the conditions on the same row. If you want them joined with an OR, you write the conditions on separate rows. In the next example below, our criteria specifies greater than 15 years membership AND Gold membership.
Handy Tips when using Advanced Filters in Excel
• When using the Advanced Filter dialog box, if you can’t remember the range of cells that hold certain values (e.g. Criteria range), you can click on Collapse Dialog which moves the focus to the workbook, enabling you to select the actual cells on the relevant worksheet.
• You can use the extract feature of the Advanced Filter to create a list of unique codes that are used in a list. For example, to create a list of Type codes, extract the Type field with no criteria and select the checkbox Unique records only in the Advanced Filter dialog box.
• When you perform the copy operation during filtering, Excel names the header row of the copied records as Extract. It names the cells containing the criteria range as Criteria. You can use these names to navigate quickly to the extract or criteria ranges of the worksheet via the Name box.
The Filter topic is covered in our Advanced Excel course
