"How To Excel" Mini-Tutorials by TheExcelAddict.com "Helping Average Spreadsheet Users Become Local Spreadsheet Experts" How To Use Excel's AutoFilter Excel has some 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 AutoFilter. Select a cell in your list and from the Data menu select Filter, AutoFilter. You will see a dropdown arrow appear beside each column heading in your list. Selecting either 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.. To remove the filter applied to a column, click on that dropdown arrow and select (All) from the list. To remove the filters applied to all columns, from the Data menu select Filter, Show All. If you want to do further work with your filtered data, you can copy and paste it somewhere else. 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. (Bonus 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 Data menu select Filter, AutoFilter. Why not print (CTRL+P) this tip and share it with your friends and associates? Get more time-saving tips just like this one delivered to you by email every week in my FREE newsletter "Spreadsheet Tips From An Excel Addict". Subscribe at the www.TheExcelAddict.com/Newsletter.htm. You'll be amazed how much time you'll save just by learning a few of these tips. Copyright © 2003 All Rights Reserved by Francis Hayes (The Excel Addict)