Excel 2007 Filter Tool

This article is part of a series to help Excel users get the most out of the tools provided to view, analyze or manipulate data, using real life examples. It is an introduction to the filter tool that focuses on how users can filter information.


Let’s look at an example. During the week, I received a roster for my soccer club noting when my team, “AA/4”, was required to setup or pack up the soccer fields on behalf of the club. The following screenshot shows a section of this roster up to week 6, which runs for a total of 14 weeks.



This excel sheet contained the Roster for every team in the club, 71 lines of information the majority of which I do not need to know and makes it difficult for me to identify my teams requirements across the whole season. The filter tool is perfect to quickly “filter” out the unwanted information (i.e. details relating to other teams) so I can easily see when my team is required to “set up” of “pack up” a ground.

However, before we begin using the filter tool we must first remove the blank rows (8, 14, 20, 22, 28, 34 in the above screenshot) as the filter tool considers an entire blank row as the end of the data set.

* Note, the filter tool can handle a blank cell within a dataset, which will be included in the category “(Blanks)”, however not an entire blank row.



Now that we have no blank rows, we can now insert the filters by highlighting the header row (cells A1 through E1) as shown below;



And then clicking on the filter tool which is contained in the “data” tab ribbon, or by clicking Ctrl +Shift + L. If you have completed this step correctly an arrow will appear next to each cell in the header row as show below;



These arrows indicate that a filter can now be applied to the data contained within that column.

By clicking on the Arrow in Column “D1” (“Set up Team”) I receive a drop down list showing all the teams listed under the “Set Up Team” column, including my team the AA/4’s as shown below;



At the moment all teams are checked. By clicking on the (Select All) tick box all teams are unchecked. Then I tick the “AA/4” box (as shown below) so that it is the only ticked option and click “OK”. Excel will then filter the list to only show the times my team is required to “set up” a field.



In this instance my team was only required to setup once, on the 20th of June 2010;



As you can see, all the other unwanted rows are hidden and only the information relating to the AA/4 conducting a “Set-up” remains. The symbol shows that a filter is being applied to column D, “Set Up Team”. To un-filter the data set, simply click on the arrow next to column D and tick the “(Select All)” box and press ok.
Once this is done I repeat the process for Column E, “Pack Up Team” to show when my team is required to pack up the grounds;



In this case, my team is required to pack up the grounds on 4 separate locations for a total of 7 occasions. .
In my next article I will explain how to use a nested IF Statement in conjunction with the filter tool to show how I filter the “Roster” information I was given to show the times when my team was required to “Set-up” or “Pack-up”, rather than requiring two separate tables, which I then save as a CSV file and import into my Google calendar.

By Matthew Brown   Popularity  (5458 Views)