Thursday, June 2, 2016

Not Your Uncle’s Filter

Most any Excel user knows how to use the Filter tool to quickly sort a table or database. Using a Filter can be very effective and can save a great deal of time in many instances.

But what if you need more Flexibility and Power, but still want to do your analysis with a fast, easy-to-use filter? The answer is, of course, the Advanced Filter (definitely Not your Uncle Jim’s filter…).

The beauty of this tool is that it is Advanced, but still very quick and easy to use. We will take a look at a couple of simple examples of how to use the Advanced Filter to help get an immediate understanding of how it works.

With the realization that this tool can work equally well with databases containing thousands of records, we will assume you have the following small database:

As is the case with any well-designed database, each field (column) has a header/name.  Not surprisingly, this is a necessity when using this tool.

Next, we will access the Advanced Filter by going to the Sort & Filter group on the Data tab, and clicking on Advanced. 

In the List range field put the location of your database (in this case it is B3: C11).

For the Criteria range, let’s assume you want to see all of the sales for the North, West, and East regions, and you want to place this filtered result below your database (use B13 for a starting point).  Simply set up a range such as in the following cells E3:E6 (or wherever you wish):

Your result will appear as follows in your chosen location:

You can also use multiple field names and criteria to extract a wealth of information in a mere three clicks.

Another one of the myriad ways you can use the Advanced Filter it to Create a Separate List of Unique Values.  If you wish to retain the original list, and create a separate list of Unique Values, you can click on our new favorite tool, and select your range. Then do the following when the menu appears:

1. Select the Copy to another location option
2. Insert the starting cell in the Copy to dialogue box
3. Put a check mark in the Unique records only box
4. Your unique items will appear in the newly-created range

Pretty Cool, you say. Absolutely! Give the Advanced Filter a 5 minutes tryout, and you’ll be Hooked!

No comments: