Microsoft Excel How-to: Using Autofilter to Filter Long Data Sets

excel logoYou've got a spreadsheet with a ton of data in it, organized by years. You want to be able to selectively filter by various years to be able to get a better picture of the data. How do you do it, without creating a slew of ranges or lookups? Simple, Autofilter to the rescue.

The following examples all refer to Microsoft Excel 2003. If you are using a version that is different than that your screens will look slightly different, but the basic functionality should be the same.
If you would like to work with the same example spreadsheet that I'm using you can download it here (right click, select save target as or save as).

Here's a screenshot of the data we'll be working with:


excel autofilter 01


That's a little tough to see, but the first column is 'Year', and the second column is 'Month'. To be able to quickly filter by these two columns (or by any of the columns, for that matter), go to the main menu and select Data -> Filter -> Autofilter:


excel autofilter 02


As soon as you do that Excel will put these nifty little drop-down boxes at the top of each of the columns:


excel autofilter 03


Click the dropdown to see one of each of the values in that column, select any of the values to filter the entire sheet by that value:


excel autofilter 04


Here's a couple of examples of the Autofilter in use, this one filtered by the year 2000:


excel autofilter 05


This one filtered by the month of December:


excel autofilter 06


This should give you a quick idea of how to use this very fast, easy way to filter the data on the spreadsheet. If you want to get rid of the filters just go back up to the main menu and select Data -> Filter -> Autofilter again to deselect it and to show all of the data again.