Wednesday, October 02, 2013

Filtering Data in Excel

Spreadsheet software has many valuable functions, but one that I find most useful (and use most frequently) is the data filter (aka “auto filter”).  This post will be about how to use the data filter in Microsoft Excel (the function is similar in most other spreadsheet programs).  For each example, I’ll be using a sample data set of medals won by Olympic athletes available here.

First, you have to turn on the filter.  To do this, highlight the row containing column names for your data set, then select Filter from the Data tab in the ribbon:


For Excel 2003 and prior, click the Data menu, then point to Filter, and click AutoFilter.

Once you’ve done this, you’ll see a button to the right of each column heading.  Clicking this button will give you a menu of options for sorting and filtering your list based on data in that column:


As an example, we’ll look for only records of gold medals in archery, ordered by year (ascending).  First, we’ll filter on the Sport column (uncheck Select All, then check Archery to do this quickly):


Next, filter the Gold Medals column for values greater than zero (there are multiple ways to do this, but I’m demonstrating the quickest for this particular data set):


Finally, sort the Year column ascending (smallest to largest):


This gets us to our result.  To remove the filter on any column, select the menu for that column and then select Clear Filter From <Column Name>:


As you can see, this is a simple but very powerful tool.  Enjoy!