The Advanced Filter

As you may well agree, there are a great many powerful gadgets in Excel that are seldom used.  The Advanced Filter is just one such example of an often-overlooked, but enormously useful tool.

The beauty of this tool is that it is, well, Advanced.  You can, however, use it in comparatively simple ways to get an immediate understanding of how it works.  We will take a look at a couple of examples of how to use the Advanced Filter.

First of all, we will assume you have the following small database (keeping in mind, of course, that this tool works equally well with databases containing thousands of records):

 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:

Okay, Cool, but don’t stop there; you can use multiple field names and criteria to extract a wealth of information in a mere three clicks.

When you’re ready to Graduate up to another level, give the Advanced Filter a try.  I think you will like it!