An Introduction to Autofilter

Since many people use Excel to maintain databases, the Autofilter feature is worth knowing about as it allows you to look at subsets of your data. The worksheet below is a listing of invoices, departments, dates and dollar amounts. New! Download this Worksheet

To use Autofilter, click in any cell in the database and select Data, Filter, Autofilter from the menu. Notice the drop down arrows that display next to the column headings. These are the means by which you will can view subsets of your data.

For example, to view all of the invoices for the Administration department, click the drop down arrow next to Department and select Administration from the list that displays. The “Administration” invoices display, and the drop down arrow is colored blue to indicate that the list is filtered by Department. The row numbers are also colored blue.

You can filter the list even further by clicking the Date drop down arrow and selecting 2/5/03 from the list of dates. Excel displays only those Administration invoices dated 2/5/03. Notice that now both the Date and Department Filter arrows are colored blue to indicate the list is filtered by Department and by date.

NOTE: If you were to print the worksheet at this point, only the filtered records would print. If you were to sort the list at this point, only the filtered records would be sorted.

To return the list to the original display, click the drop down arrow next to Administration and select “All”. Do the same for the Date drop down arrow.

Custom Filters

Selecting Custom from any of the drop down arrows displays the Custom Filter dialog box, which allows you to go beyond the standard autofilter options. In the example below, the dialog box is completed to indicate that invoices for Accounting or Computing should be filtered.

The results:

To return the list to its original format, click the appropriate drop down arrow and select All.

To turn off Autofilter, click in the list and select Data, Filter, Autofilter from the Excel menu.