If you use AutoFilter to search for data in a list, arrows appear to the right of the column headers in the filtered list. For filtered data, the row numbers are highlighted in blue, and the AutoFilter arrow changes into a funnel (filter) symbol next to the fields by which the data has been filtered.
In the AutoFilter drop-down list you can:
- Sort data by selecting Sort Smallest to Largest or Sort Largest to Smallest.
- Sort data by color, using the Sort by Color command; in the drop-down list next to this command, you can define a custom sort order.
- Remove a filter from a field, using the command Clear Filter From « Field_Name ».
- Apply a filter by color, using the Filter by Color command.
- Specify a numeric range for filtering data, using Number Filters. In the drop-down list next to this command, you can select Custom Filter, which opens the Custom AutoFilter dialog box, where a simple filter criterion with up to two conditions can be set.
- Search for data using the Search box by entering a criterion for exact match or approximate match using a pattern.
- Select a field value for an exact match search.
Searching with AutoFilter is done in the following order:
- Place the cell pointer within the data list.
- Go to the Home tab of the ribbon, in the Editing group click the Sort & Filter button and select Filter, or use the Filter command located in the Sort & Filter group on the Data tab of the ribbon. AutoFilter arrows will appear next to each field in the header row of the list.
- Go to the required field.
- Choose the desired search criterion or use the Custom AutoFilter. Note that the Custom AutoFilter dialog box allows you to quickly set more complex conditions than a simple comparison. In the top-left drop-down list, select a comparison operator (in this case, greater than or equal to), and in the right-hand field, enter or select a value (here: 9000). Then, if necessary, choose either the AND or OR operator, set the second comparison operator and value. In this example, the AND option is chosen, the operator less than, and the value 20000. Finally, click OK to obtain the result of the custom filter.
- To include another field in the criterion, return to step 3.
