|
Excel's AutoFilter feature literally makes filtering out unwanted data in a data list as easy as clicking a button. When the cell pointer is located within any cell in your data list and you choose the Data --> Filter --> AutoFilter command from the Excel menu bar, the program adds drop-down buttons to each of the field names in the top row of the list, as shown in Figure 1.
Figure 1: Employee data list with the AutoFilter feature turned on.
When you click a drop-down button next to a field, Excel displays a menu that contains the following three items at the top of the menu:
- All: Displays all records with an entry in that field
- Top 10: Displays only the records with the top 10 values or in the top 10 percent
- Custom: Opens the Custom AutoFilter dialog box, where you can specify multiple criteria for filtering the list by using either an AND or OR condition as well as criteria using logical operators, such as "is greater than," "is less than," "begins with," "ends with," and so on
Following the Custom item, the field's drop-down list box displays all the unique entries in that field in ascending order (lowest to highest in numeric and date fields and A to Z in text fields). To filter out all the records in the list except those that contain a particular entry or value, click that entry or value on the field's pop-up menu.
For example, Figure 2 shows the Employee data list after selecting the entry, Accounting, on the Dept field's pop-up menu. As this figure shows, Excel has now hidden all the rows in the data list containing records where the entry in the Dept field was anything but Accounting, thus essentially filtering the list down to just those records for the employees who work in Accounting.
Figure 2: Employee data list after filtering out all records except those where the Dept is Accounting.
To redisplay the entire data list, you can click the (All) item at the very top of the Dept field's pop-up menu, or you can choose the Data --> Filter --> Show All command from the Excel menu bar. When you're finished filtering the data list, you can then remove the drop-down buttons from the field names by choosing Data --> Filter --> AutoFilter on the pull-down menus (this command acts like a toggle switch so that selecting it a second time turns it off).
Making it to the Top Ten!
When the AutoFilter feature is turned on, you can select the Top Ten item at the top of a field's pop-up menu to filter out all records except those whose entries in that field are at the top or bottom of the list by a certain number (10 by default) or in a certain top or bottom percent (10 by default). Of course, you can only use the Top Ten item in numerical fields and date fields; this kind of filtering doesn't make any sense when you're dealing with entries in a text field.
When you click the Top Ten item on a numeric or date field's pop-up menu, Excel opens the Top 10 AutoFilter dialog box, shown in Figure 3, where you can specify your filtering criteria. By default, the Top 10 AutoFilter dialog box is set to filter out all records except those whose entries are among the top ten items in the field by selecting Top in the drop-down list box on the left, 10 in the middle combo box, and Items in the drop-down list box on the right. If you want to use these default criteria, you simply click OK in the Top 10 AutoFilter dialog box.
Figure 3: Using the Top 10 AutoFilter dialog box to filter out all records except for those with the top ten salaries.
You can also change the filtering criteria in the Top 10 AutoFilter dialog box before you filter the data. You can choose between Top and Bottom in the leftmost drop-down list box and between Items and Percent in the rightmost one. You can also change the number in the middle combo box by clicking it and entering a new value or using the Spin buttons to select one.
Custom AutoFilter at your service
You can use the Custom item on a field's pop-up menu to open the Custom AutoFilter dialog box, where you can specify more complex filtering criteria by using conditions with the AND and OR logical operators (called AND and OR conditions for short). When you click the Custom item on a field's pop-up menu, Excel opens the Custom AutoFilter dialog box, similar to the one shown in Figure 4.
Figure 4: Using Custom AutoFilter to filter out records except for those within a range of salaries.
Here, you select the type of operator to use in evaluating the first and the second condition in the top and bottom drop-down list boxes and the values to be evaluated in the first and second condition in the associated combo boxes. You also specify the type of relationship between the two conditions with the And or Or radio buttons (the And radio button is selected by default).
When selecting the operator for the first and second condition in the leftmost drop-down list boxes at the top and bottom of the Custom AutoFilter dialog box, you have the following choices:
- Is greater than or equal to
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 values 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 on the right side of the Custom AutoFilter dialog box, you can type in the text, number, or date, or you can select an existing field entry by clicking the box's drop-down list button and then clicking the entry on the pop-up menu.
Figure 4 illustrates setting up filtering criteria in the Custom AutoFilter dialog box that selects records whose Salary values fall within a particular range of values. This example uses an AND condition to filter out all records where the salaries are below $40,000 and above $75,000 by entering the following complex condition:
Salary is greater than or equal to 40000 AND is less than or equal to 75000
You can also use the Custom AutoFilter feature to create an OR condition where records are displayed if they contain a value or an entry that meets either one of two conditions. For example, suppose that you wanted to see only 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 pop-up menu. Then, select the Equals operator in both condition drop-down list boxes, select Boston and then Chicago in the respective combo boxes, and then click the Or radio button to create the following complex condition:
Location equals Boston OR equals Chicago
| 
