Data Filtering - Advanced (Custom) Filters

The Advanced Filter command can filter a range in place like the AutoFilter command, but it does not display drop-down lists for the columns. Instead, you type the criteria you want to filter by in a separate criteria range above the range. A criteria range allows for more complex criteria to be filtered.

You are not limited to the simple choices that AutoFilters provide (for example, filtering by the "Art" or "History" dept in the example below). You can use Custom Filters to select a subset of records based on more detailed criteria.

For example, to create a custom filter for the column BEN (benefits) to select any record that contains an "R" in that field's text, you would:

1. Turn on AutoFilters (if it is off) by selecting Data, Filter, AutoFilter.

2. Using the down arrow for BEN, choose "Custom..." as your filter. Macintosh - choose "Custom Filter".

3. The Custom AutoFilter dialog box will appear. Here you can select the type of comparison operator on the left to use along with the corresponding drop-down list value on the right. You may also manually enter the value. In this example, we'll select 'contains' in the left column and type an 'R' in the right field.

4. The filtered list that is returned lists each record that contains an R in the BEN field:

Here are a few more examples of different comparison operators used in custom filters:

Filter: Gross Pay is between $350 and $500. *This would includes those Gross Pay records that were exactly 350 or 500.

Filter: Last names that begin with a "B".

Filter: Last names are between B and G (notice the 'or equal to'). If we wanted to include last names that begin with an 'H', we would have to use the 'is less than or equal to' operator.

Filter: Here we want to see all records EXCEPT those in the Art department.