How To Use Advanced Filters In Excel And VBA

Excel macros

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

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


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

4 Feedbacks on "How To Use Advanced Filters In Excel And VBA"

Humberto

Your website must supply a clear message having a goal-oriented direction, setting you apart from
your competitors. This business is going to be operated
from the owner’s home and will probably be done entirely about the internet.
Donnie Jonston could be the author of this information about
the best way to make money on Ebay Donnie has years of work experience
as being a writer as well as working with drop shippers in the variety of entrepreneurial
ventures.



http://www.arteks.org.pl/

Bulks of the advertisers are primarily private house owners, letting
managers and property agents. So unless any organization completes the formalities and get the
necessary certifications they are able to’t start their business.

The business degree raises one’s social standing: in simple terms, it opens for you doors that might have otherwise remained closed
for you.



best way to make money on ebay | D$ Domination

[…] d'Ĺ’uvres – An original mixtape by VendrediInteresting ways to earn a lot by sitting at home!How To Use Advanced Filters In Excel And VBA ul.legalfooter li{ list-style:none; float:left; padding-right:20px; } .accept{ display:none; […]



Feedback Questionaire

Shopper Approved is based on the powerful concept called
‘social proof’, which states that:

When a potential customer goes online to buy a product or service, they actually want and actively
look for reassurance that they’re making the right decision. And the way they get that assurance is
from other customers’ ratings, feedback, and reviews.

This is why statistically, 77% of online shoppers turn to
consumer reviews before they decide to buy!

There’s a reason why Amazon, eBay, Sears, and pretty much every other
major ecommerce site have customer
ratings and reviews integrated into their website. It’s because online reviews have
been proven to increase
conversion, increase average transaction amounts, improve customer
satisfaction, and even lower product
returns!

Check Out The Link In Signature



Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.