Creating Custom Filters
EXCEL’S AUTOFILTER FEATURE is fast and accurate, but it’s not always enough. Custom filters make it possible to compare parts of your data in order to display exactly the information you are looking for.
Filtering Text
To filter your text data, click the appropriate filter arrow and choose Text Filters. Make a selection from the list of available filters, enter your specific criteria in the resulting dialog box and click OK to apply the filter (see Figure 8-20).
Figure 8-20. Results of a text filter containing “student” in the Comments column.
Table 8-2 describes each of these filters and the results that would be produced by filtering an address book database.
| Filter | Sample Filter | Records Displayed |
|---|---|---|
| Equals | Illinois | Anyone with an address in Illinois |
| Does Not Equal | Massachusetts | Anyone with an address anywhere other than Massachusetts |
| Begins With | New | Anyone with “New” at the beginning of the address. For example, New York, New Jersey, New Hampshire, and New Mexico |
| Ends With | ota | Anyone with “ota” at the end of the address. For example, South Dakota, North Dakota, and Minnesota |
| Contains | iss | Anyone with “iss” anywhere in the address. For example, Mississippi and Missouri |
| Does Not Contain | nn | Anyone without “nn” in the address. For example, Wisconsin, but not Pennsylvania |
Tip
The Custom AutoFilter dialog box provides yet another more specific filter opportunity. Suppose you want to search for a word that begins with M, but is only eight characters long. Use the question mark symbol as a wildcard. For example, if you enter M???????, Excel will return Missouri, Maryland, and Michigan. The ? takes the place of a single character. Use an asterisk (*) as a wildcard if you don’t know the exact number of characters.
Adding Multiple Comparison Criteria
You can use the Custom AutoFilter dialog box (see Figure 8-21) to compare more than one criterion. Excel uses the comparison operators AND and OR to combine comparison filters. For instance, creating a custom filter that begins with M and ends with I would find both Mississippi and Missouri. A custom filter that begins with F or begins with G would find both Florida and Georgia.
To create a Custom AutoFilter, select your initial filter using one of the methods described in this chapter. Then, in the Custom AutoFilter dialog box, choose the AND or OR option. From the second drop-down menu, select another filter option and type your filter term in the box. Click OK to apply the filter.
Using PivotTables on ColumnsAll of Excel’s filters temporarily hide rows that contain the data you want to filter out of the worksheet. The filters cannot be applied to columns. If you need to filter data from a column, you can create a PivotTable to reposition your data. Learn how to do this in Chapter 15 “Using PivotTables.” |
Caution
| The AutoFilter feature hides rows that don’t meet your criteria, including rows holding your summary data. Be sure to check the summary row in your filter criteria if you want to display a calculation of the entire database, not just your filtered data, as shown in Figure 8-22. Figure 8-22. The SUM formula will not recognize filtered data. Clearly 5+10 does not equal 2,175.
|