Customizing the AutoFilter Settings

The basic AutoFilter feature doesn't let you select more than a single filtering criterion for any one field in the list. This means, for example, that you can use the basic AutoFilter to filter the Employee list for a particular salary such as $55,000 but you can't filter the list for salaries in the range between $55,000 and $75,000. To filter a data list for a range of values in a particular field, you need to use the Custom AutoFilter feature.

To invoke Custom AutoFilter, you need to select the (Custom) item at the top of the field's drop-down list. For the salary example, for example, you do this by clicking (Custom) in the Salary field's drop-down list. When you select this item, a Custom AutoFilter dialog box appears.

Here, you select the type of operator to use in evaluating the first and the second condition in the top and bottom drop-down lists and the values to be evaluated in the first and second condition in the combo boxes. You also specify the type of relationship between the two conditions with the And or Or options. (The And option is selected by default.)

When selecting the operator for the first and second condition in the leftmost drop-down lists, you have the following choices:

• Equals
• Does Not Equal
• Is Greater Than
• Is Greater Than or Equal To
• Is Less Than
• Is Less Than or Equal To
• Begins With
• Does Not Begin With
• Ends With
• Does Not End With
• Contains
• Does Not Contain

Note that you can use the Begins With, Ends With, and Contains operators and their negative counterparts when filtering a text field. You can also use the question mark (?) and asterisk (*) wildcard characters when entering the characters for use with these operators. (The question mark wildcard stands for individual characters, and the asterisk stands for one or more characters.) You use the other logical operators when dealing with numeric and date fields.

When specifying the values to evaluate in the associated combo boxes, you can type in the text, number, or date, or you can select an existing field entry by selecting one from the drop-down list.

You can also use the Custom AutoFilter feature to create an OR condition where records appear if they contain either a value or entry that you specify in any one of the two conditions. For example, suppose that you want to create a subset with the records in the data list where the location is Boston or Chicago. To do this, you open the Custom AutoFilter dialog box from the Location field's drop-down list. Then select the Equals operator in both condition top and bottom drop-down lists, select Boston and then Chicago in the respective combo boxes, and then select the OR condition.