You can construct a custom AutoFilter. To do this, select the Text Filter command from the table menu and choose one of its text filtering options. No matter which text filtering option you pick, Excel displays the Custom AutoFilter dialog box. This dialog box enables you to specify with great precision what records you want to appear on your filtered list.
To create a custom AutoFilter, take the following steps:
Turn on the Excel Filters.
Filtering is probably already on because you’ve created a table. However, if filtering isn’t turned on, select the table, click the Sort & Filter button, and choose Filter. Or in Excel 2007 or Excel 2010, simply choose Data→Filter.
Select the field that you want to use for your custom AutoFilter.
To indicate which field you want to use, open the filtering drop-down list for that field to display the table menu, select Text Filters, and then select a filtering option. When you do this, Excel displays the Custom AutoFilter dialog box.
Describe the AutoFilter operation.
To describe your AutoFilter, you need to identify (or confirm) the filtering operation and the filter criteria. Use the left-side set of drop-down lists to select a filtering option. For example, the filtering option selected in the first Custom AutoFilter set of dialog boxes is Begins With. If you open this drop-down list, you’ll see that Excel provides a series of filtering options:
Begins With
Equals
Does Not Equal
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
The key thing to be aware of is that you want to pick a filtering operation that, in conjunction with your filtering criteria, enables you to identify the records that you want to appear in your filtered list. Note that Excel initially fills in the filtering option that matches the command you selected on the Text Filter submenu, but you can change this initial filtering selection to something else.
In practice, you won’t want to use precise filtering criteria. Why? Well, because your list data will probably be pretty dirty. For example, the names of stores might not match perfectly because of misspellings. For this reason, you’ll find filtering operations based on Begins With or Contains and filtering criteria that use fragments of field names or ranges of values most valuable.
Describe the AutoFilter filtering criteria.
After you pick the filtering option, you describe the filtering criteria by using the right-hand drop-down list. For example, if you want to filter records that equal Sams Grocery or, more practically, that begin with the word Sams, you enter Sams into the right-hand box.
You can use more than one AutoFilter criterion. If you want to use two custom AutoFilter criteria, you need to indicate whether the criteria are both applied together or are applied independently. You select either the And or Or radio button to make this specification.
Click OK.
Excel then filters your table according to your custom AutoFilter.