Excel Cheats: Part Two

This post is Part Two of a series on Excel Cheats: What You Didn’t Know! Today I will show you how to use the AutoFilter feature, which will allow you to easily search and organize information (columns) in your spreadsheet. First, what is an AutoFilter? In the image below, you can see that an AutoFilter inserts a drop-down arrow in the cell and allows you to organize the contents of that column in a variety of ways (i.e., Ascending, Descending, Top 10, etc.). This is a great feature if you want to easily sort or identify data in a specific column. The AutoFilter function also allows you to specify which rows you want displayed, the other rows in the worksheet will be hidden.

Example of AutoFilter in Excel 2010

To Add a Filter to a Column Header
Highlight the column that you would like to filter. Click on the Data tab, then in the Sort & Filter section, click Filter. If your worksheet has multiple columns and you would like all header columns (on the same row) to include an AutoFilter, then bring your cursor to one of the header cells before you click Filter. Excel should automatically find and recognize all of your header columns and add an AutoFilter for you.

Data Tab in Excel 2010

Once you use the drop-down arrow on a particular column to filter that selection, you will notice that the box including the drop-down arrow changes to a drop-down arrow and a symbol of the type of filter that you created. See below: the boxes that are highlighted black have been filtered and you can see that something else is included besides the drop-down arrow. This lets you know that you have filtered these specific columns.

AutoFilter On

To Turn AutoFilter Off: Simply go back to the Data tab and click Filter again in the Sort & Filter section. This will turn off and remove all AutoFilters for that worksheet. Check back again soon for the next Excel Cheat tip!