We will use a simple example to demonstrate how to use Auto Filter.
You have a column of sales figures in the range B11..B22, headed by a label (Sales $) in B10. A column of months (January to December) in the range A11..A22 precedes the Sales column and is headed by a label, Months, in A10.
To select all of the rows with a value greater than a certain value in the Sales column, for example, $10,000, do the following:
Select the Sales column, including the label, using your mouse.
Select Filter and then Auto Filter from the Data menu. This puts a box with a drop-down list at the top of the selected range i.e. in this case in the Sales cell.
Click on the arrow to access the drop-down list and then choose from the following:
All: displays all of the rows in the selected range.
Top 10: opens a box in which you can choose how many records to display and whether they should be from the top or bottom of the selected range.
Blanks: displays only those rows that have a blank cell in the selected range.
Non-blank: displays only those rows that have a value in the selected range.
Custom: opens up the Custom AutoFilter box:
Here you can choose from a standard set of comparisons in the left-hand drop-down list to filter the rows in the selected range: equals, does not equal, is greater than, and so on. The value used for the comparison can be selected from the right-hand drop-down list, which shows all the values from the selected range, or can be typed in by yourself if it is not included in the range of values e.g. is less than a particular value typed by you. You can combine filters through using either an And or an Or operator: And enables you to set two conditions, both of which must be fulfilled for a value to be selected e.g. value is to be greater than x and less than y; Or will select a value if either one (or both) of the conditions is fulfilled e.g. value is to be less than x or equal to y. (Note that you can substitute a wildcard for a missing character (use ?) or characters (use *) e.g. "equals 3??" will select any 3-digit string beginning with a 3; "equals *d" will select any string ending in d, no matter what its length.) Click on OK to apply your Custom AutoFilter.
To show all records after you have applied a filter, select Show All from the Data/Filter menu.
To cancel a filter, select Auto Filter from the Data/Filter menu to switch if off.
See also: Advanced filter