AutoFilter (found under the Data > Filter menu) provides an easy way to limit the data displayed in a list. It’s less flexible than its cousin Advanced Filter, but what it lacks in flexibility, it makes up in ease of use.
Select the range you want to filter and choose Data > Filter > AutoFilter. You can select one cell in the range, and as long as the range is contiguous, Excel will guess which range you want to filter. Note that the first row of your range must contain headings. If it doesn’t, Excel will assume that the first row is headings, which may be undesirable. Once you’ve selected the menu item, Excel inserts dropdown boxes into cells of the header row.
Clicking on the dropdown will present a list of possible values. Most of the values will likely be actual values from the list. The other values are All, Top 10, and Custom.
In this example, I select 3 to only show those rows whose SupplierID equals 3.
The All choice is used to clear the filter for that column. Top 10 is used to show the Top or Bottom 10 items. A dialog appears when you select that choice that allows you to pick Top or Bottom and Items or Percent. Happily, the 10 isn’t fixed either; you can select a number from 1 to 500, although I’m not sure what use 500% would be.
The Custom choice gives a little more flexibilty to your filter in that you can choose greater than, less than, and all those comparison operators that you know and love.
Debra at Contextures says the limit on the number of choices the dropdown will show is 1000, and I have no reason to doubt her. She also has some lovely pages about AutoFilter, so if you’re interested in learning more, I recommend them.
You can filter on more than one column also. Just set the filter for each column separately. From the example above, once you’ve limited the list to SupplierID=3, you can further limit the list, for instance, with the Custom choice and only show those items with SupplierID=3 AND UnitPrice > 26.
Filtering on multiple columns is an AND proposition. If you need to use an OR between multiple columns, you’ll either need a helper column or the Advanced Filter, also under the Data > Filter menu.
Finally, to clear the dropdowns, select AutoFilter from the Data > Filter menu again and it will toggle off.