Using AutoFilter to Find Records

When you want to hide all the rows in your list except those that meet certain criteria, you can use the AutoFilter command on the Filter submenu of the Data menu. The AutoFilter command places a drop-down list box at the top of each column in your list. To display a particular group of records, select the criteria that you want in one or more of the drop-down list boxes. For example, to display the sales history for all employees that had $1,000 orders in January, you could select January in the Month column drop-down list box and $1,000 in the Sale drop-down list box.

To use the AutoFilter command to find records, follow these steps:

1. Click any cell in the list.
2. From the Data menu, choose Filter, and then choose AutoFilter from the submenu. Each column head now displays a down arrow.
3. Click the down arrow next to the heading that you want to use for the filter. A list box that contains filter options appears, similar to the one shown in Figure 22-4.
4. If a column in your list contains one or more blank cells, you'll also see (Blanks) and (NonBlanks) options at the bottom of the list. The (Blanks) option displays only the records containing an empty cell (blank fields) in the filter column, so that you can locate any missing items quickly. The (NonBlanks) option displays the opposite— all records that have an entry in the filter column. Click the value that you want to use for the filter.



Figure 22-4. When you choose the AutoFilter command, it places filter arrows at the top of each column in your database.

Excel hides the entries that don't match the criterion you specified, and highlights the active filter arrow. Figure 22-5 shows the results of using the January criterion in the Month column.

Figure 22-5. Click the filter arrows to display only the records you want.

You can use more than one filter arrow to display only the records that you want— an extremely useful strategy if your list is many records long. To continue working with AutoFilter but to also redisplay all your records, choose Show All from the Filter submenu of the Data menu. Excel will display all your records again. To remove the AutoFilter drop-down list boxes, disable the AutoFilter command on the Filter submenu.

Creating a Custom AutoFilter

When you want to display a numeric range of data, or customize a column filter in other ways, choose Custom from the AutoFilter drop-down list box to display the Custom AutoFilter dialog box. The dialog box contains two relational list boxes and two value list boxes that you can use to build a custom range for the filter. For example, you could display all sales greater than $1,000 or, as shown in Figure 22-6, all sales between $500 and $800. The list boxes are easy to deal with because the most useful values and relationships are already listed in them— all you have to do is point and click. You can further fine-tune your criteria by using the And and Or option buttons as well as the ? and * wildcard characters.

Figure 22-6. The custom AutoFilter dialog box lets you build your own filter.

To create a custom AutoFilter, follow these steps:

1. Click any cell in the list.
2. If AutoFilter isn't already enabled, choose Filter from the Data menu, and then choose AutoFilter from the submenu. A drop-down list box appears at the top of each column in the list.
3. Click the arrow next to the heading that you want to use for the customized filter, and select (Custom) from the list of choices. The Custom AutoFilter dialog box opens.
4. Click the first relational operator list box and specify the relationship (equals, is greater than, is less than, and so on) that you want to use for the filter, and then click the first value list box and specify the boundary that you want to set. (For example, you could specify all values greater than or equal to $500 with is greater than or equal to $500.)
5. If you want to specify a second range, click And to indicate that the records must meet both criteria, or click Or to indicate that the records can match either criterion. Then specify a relationship in the second relational operator list box and a range boundary in the second value list box. Figure 22-6 shows a Custom AutoFilter dialog box with two range criteria specified.
6. Click OK to apply the custom AutoFilter. The records selected by the filter are displayed in your worksheet.