How To Use Advanced Filters In Excel And VBA

Anyone who has worked with large sets of data in excel has used filtering to get a better look at the data. Take an example where I have the list of the top 200 female tennis players in the world and need to find players that fit a specific criteria.

The easier method is to simply select the cells at the top of the columns:

And then click: “Data/Filter/Auto-Filter

Then, I can simply select a criteria such as a specific country or even select a “custom filter” where I can select up to two conditions that must be met.

That certainly gives you a lot of different possibilities right? Take a look at a filter on all Russian players:

There are however many reasons why that might not be enough. What if for example you wanted to specify 3 different countries? Or you wanted to quickly process a list of different queries? Or you have very custom data set queries?

In Comes Advanced Filters In Excel VBA

It is a simple feature, very easy to use but it’s one of the most powerful functions in excel in my opinion. How does it work? You need a few things to have this work. If you are not familiar with excel macros, it might be a good idea to take a look at our excel macros introduction. Back to advanced filtering though, in order to get it done, you need:

-For the data set to have headers
-Right next to it (or it can be located elsewhere), you need to have the same headers, here is my example:

Then, simply enter the criteria that you would like. In this example, I would like all players from these countries:

RUS
ITA
CZE
POL
GER

You can see a screenshot here:

Here is the code that I’m using, as you can see it’s very simple.

There are 3 ranges that you can “edit”:

-the first one is where the data that you are filtering is found
-the second one is where the criteria should be found. You need to edit the end of the range (in this case O6) to only include lines that have a criteria. If I had put O7, that would have meant including an “empty” criteria – this is critical
-the third one is where you’d like to display results

The result is quite extensive, you can see a shot here.

I could also add additional criteria points, for example, only including players that have played over 25 tournaments as you can see here:

The new result is:

It is extremely powerful and you can use it very large sets of data, adding multiple types of crtieria, etc. It is also very quick.

As always, you can download this spreadhseet here!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss

***************************************************