Excel provides an AutoFilter command that’s pretty cool. When you use AutoFilter, you produce a new table that includes a subset of the records from your original table. For example, in the case of a grocery list table, you could use AutoFilter to create a subset that shows only those items that you’ll purchase at Butchermans or a subset table that shows only those items that cost more than $2.
To use AutoFilter on a table, take these steps:
Select your table.
Select your table by clicking one of its cells. By the way, if you haven’t yet turned the worksheet range holding the table data into an “official” Excel table, select the table and then choose the Insert tab’s Table command.
Choose the AutoFilter command.
When you tell Excel that a particular worksheet range represents a table, Excel turns the header row, or row of field names, into drop-down lists. If your table doesn’t include these drop-down lists, add them by clicking the Sort & Filter button and choosing the Filter command. Excel turns the header row, or row of field names, into drop-down lists.
Tip: In Excel 2007 and Excel 2010, you choose the Data→Filter command to tell Excel you want to AutoFilter.
Use the drop-down lists to filter the list.
Each of the drop-down lists that now make up the header row can be used to filter the list.
To filter the list by using the contents of some field, select (or open) the drop-down list for that field. For example, you might choose to filter the grocery list so that it shows only those items that you’ll purchase at Sams Grocery.
To do this, click the Store drop-down list down-arrow button. When you do, Excel displays a menu of table sorting and filtering options. To see just those records that describe items you’ve purchased at Sams Grocery, select Sams Grocery.
You might even be able to see a little picture of a funnel on the Store column’s drop-down list button. This icon tells you the table is filtered using the Store columns data.
To unfilter the table, open the Store drop-down list and choose Select All.
If you’re filtering a table using the table menu, you can also sort the table’s records by using table menu commands. Sort A to Z sorts the records (filtered or not) in ascending order. Sort Z to A sorts the records (again, filtered or not) in descending order. Sort by Color lets you sort according to cell colors.