Excel Tips for Excel Addicts by TheExcelAddict.com "Helping Average Spreadsheet Users Become Local Spreadsheet Experts" How To Use Excel's AutoFilter Commands Excel has a lot of great tools that allow you to do amazing things with your data. The sad thing is that most Excel users spend years working with Excel without ever knowing they even exist. One such tool is Excel's AutoFilter. If you work with lists of data in Excel (and who doesn't?), AutoFilter is essential. I'll cover the basics here. First you must have a list. The first row must contain headings. The list can be any number of columns wide and any number of rows high. There should be no blank rows in the list. There should be a blank column to the right and a blank row to the bottom of the list. If the list doesn't start in row 1 there must be a blank row above the headings. If the list doesn't start in column A, there should be a blank column to the left of the list. To get started you need to turn on the AutoFilter. Select a cell in your list. If you're using Excel 2007, click on the Data tab and click the Filter command. For Excel 2000-2003 users, click the Data menu and select Filter, AutoFilter. You will see a dropdown arrow appear beside each column heading in your list. Selecting a dropdown arrow will present a list you can choose from to filter your data to show just the rows that match the criteria you select. All other rows are temporarily hidden. You can further filter your list by selecting another dropdown arrow. Notice that blue dropdown arrows identify the columns you have applied filters to and blue row numbers identify the rows that meet the filter criteria.. If you want to do further work with your filtered data, you can copy (CTRL+C) and paste (CTRL+V) it somewhere else. To remove the filter applied to a column, click on that dropdown arrow and in Excel 2007, click (Select All) or in Excel 2000-2003 select (All) or from the list. To remove the filters applied to all columns, from the Excel 2007 Data tab click the Clear command. or from the Excel 2000-2003 Data menu select Filter, Show All. If you want to calculate amounts for your filtered data, add a SUBTOTAL formula two rows below your list in the column you wish to calculate. Do this before applying any filters. Your subtotal formula will look like =SUBTOTAL(9,D1:D50) where D1 is the first row in your list and D50 is the last row in your list. Now, when you apply filters to your list, the subtotal formula will automatically calculate the total for only those records matching your filter criteria. (Excel Addict Trick: For ad hoc analysis of your data, instead of putting the subtotal formula below your data, insert a couple of rows above your data and add the subtotal formula there. Then you won't have to scroll all the way to the bottom after applying each filter.) This is powerful! To remove the filters applied to all columns and turn off AutoFilter, from the Excel 2007 Data tab click the Filter command. For Excel 2000-2003 users, from the Data menu select Filter, AutoFilter.  Click HERE for more FREE Time-Saving Excel tips. Copyright © 2003 All Rights Reserved by Francis Hayes (The Excel Addict)