AutoFilter Basics

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 in literally a few button clicks after turning the feature on. To turn AutoFilter on, position the cell pointer somewhere in one of the cells of the list and choose Data → Filter → AutoFilter. When you select this command, Excel indicates that AutoFilter is on by displaying dropdown 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 the 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 dropdown button blue to indicate that the field is being used in filtering the data list.

To further filter the list and display only those records where the Dept is Accounting and the Profit Sharing is No, click the drop-down button on the Profit Sharing field name and then select No in its drop-down list. If you then want to filter the list even further to display only the records where the Dept is Accounting, Profit Sharing is No, and the Location is Detroit, click the Location field's dropdown button and select Detroit in its drop-down list.

When you filter a data list to create a subset in this manner, Excel only hides the records that don't meet your filtering criteria - no rows are actually deleted from the workbook. To redisplay all the hidden rows with the missing records, choose Data → Filter → Show All. If you're filtering the list using the criteria from only one field (column), you can also do this by clicking (All) near the top of that field's dropdown list.

You can also redisplay all the records in the data list by turning AutoFilter off (Data → Filter → AutoFilter).

If you filter your data list with multiple criteria and only want to redisplay the records hidden by a particular criterion, click the (All) item in that field's drop-down list.

Excel's AutoFilter buttons enable you to sort a subset of the data list on different sorting keys. To sort a subset in ascending order on a particular field, select the Sort Ascending item at the top of its drop-down list. To sort in descending order, select Sort Descending instead. To sort the subset on more than one key, select either the Sort Ascending or Sort Descending item on the various fields' drop-down lists in the order appropriate for sorting duplicates.