The Excel 2013 AutoFilter Feature

By Greg Harvey from Excel 2013 All-in-One For Dummies

Excel’s AutoFilter feature makes filtering out unwanted data in a data list as easy as clicking the AutoFilter button on the column on which you want to filter the data and then choosing the appropriate filtering criteria from that column’s drop-down menu.

If you open a worksheet with a data list and you don’t find AutoFilter buttons attached to each of the field names at the top of the list, you can display them simply by positioning the cell pointer in one of the cells with the field names and then clicking the Filter command button on the Ribbon’s Data tab or pressing Ctrl+Shift+L or Alt+AT.

The filter options on a column’s AutoFilter drop-down menu depend on the type of entries in the field. On the drop-down menu in a column that contains only date entries, the menu contains a Date Filters option to which a submenu of the actual filters is attached.

On the drop-down menu in a column that contains only numeric entries (besides dates) or a mixture of dates with other types of numeric entries, the menu contains a Number Filters option. On the drop-down menu in a column that contains only text entries or a mixture of text, date, and other numeric entries, the menu contains a Text Filters option.

In addition to the Date Filters, Text Filters, or Number Filters options (depending on the type of field), the AutoFilter drop-down menu for each field in the data list contains a list box with a complete listing of all entries made in that column, each with its own check box. At the most basic level, you can filter the data list by clearing the check box for all the entries whose records you don’t want to see in the list.

This kind of basic filtering works best in fields such as City, State, or Country, which contain many duplicates, so you can see a subset of the data list that contains only the cities, states, or countries you want to work with at the time.

The easiest way to perform this basic type of filtering on a field is to first deselect the check box in front of the (Select All) option at the top of the field’s list box to clear the check boxes, and then select each of the check boxes containing the entries for the records you do want displayed in the filtered data list.

After you finish selecting the check boxes for all the entries you want to keep, you click OK to close the AutoFilter drop-down menu.

Excel then hides rows in the data list for all records except for those that contain the entries you just selected. The program also lets you know which field or fields have been used in the filtering operation by adding a cone filter icon to the column’s AutoFilter button.

To restore all the records to the data list, you can remove the filtering by clicking the Clear command button in the Sort & Filter group of the Data tab of the Ribbon or by pressing Alt+AC.

When doing this basic kind of list filtering, you can select specific entries from more than one field in this list. Here, you want only the employees in the company who work in the Engineering and Information Services departments in the Chicago and Seattle offices.

To do this, select only the Engineering and Information Services entries in the list box on the Dept field’s AutoFilter drop-down menu and only the Chicago and Seattle entries in the list box on the Location field’s AutoFilter drop-down menu.

As you can see, after filtering the Employee data list so that only the records for employees in either the Engineering or Information Services department in either the Chicago or Seattle office locations are listed, Excel adds the cone filter icon to the AutoFilter buttons on both the Dept and Location fields in the top row, indicating that the list is filtered using criteria involving both fields.

Keep in mind that after filtering the data list in this manner, you can then copy remaining records that make up the desired subset of the data list to a new area in the same worksheet or to a new sheet in the workbook.

You can then sort the data (by adding AutoFilter buttons with the Filter command button on the Data tab), chart the data, analyze the data, or summarize the data in a pivot table.