|
Excel's AutoFilter feature is so simple to use that you can set up the filtering criteria and display the subset of the data list that meet those criteria with just a few button clicks. To turn AutoFilter on, position the cell pointer somewhere in one of the cells of your data list and choose Data --> Filter --> AutoFilter. When you select this command, Excel indicates that AutoFilter is on by displaying drop-down buttons to the right of each field name (column heading) in the data list.
 | To filter the list, you simply click the drop-down button for each field (column) that you want to use as filtering criteria and then select the entry you want to use on its drop-down list. For example, to filter an Employee list so that you only see records where the Dept field is Accounting, click the Dept field's drop-down button and then select Accounting in its drop-down list. Excel then hides the rows for all records where the department is not Accounting, leaving only those where the Dept field contains Accounting displayed. The program also colors the downward-pointing triangle in the Dept field's drop-down button blue to indicate that the field is being used in filtering the data . |
But what if you want to use AutoFilter to find a set of data that fits a whole range of criteria, not just one? The basic AutoFilter feature in Excel 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. When you select this item, a Custom AutoFilter dialog 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:
- 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 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.
|
