Advanced Filtering in Microsoft Excel


Take a guess… how many rows will have been filtered in Excel by now? I have no idea but I do know that filtering is used by millions of excel users worldwide. Why do we use this feature so much? I think that it’s because it has such a low learning curve and you get an instant result. The way it is presented is easy for the end user and you can apply multiple filters in one set of data. So why is there the distinction between Filter and Avanced Filter? To get a clear view on filtering it is important to know how to apply your filter. This is always a great topic in my Excel courses. We start of by applying the simple filters and soon I start asking questions which make filtering a lot harder than when we started. Let me explain.

Basic Filtering
Let’s take a look at an example. In the list below we are going to apply a basic filter by looking for Children’s Books. Go to the little arrow in the header of the category column, and in the dropdown
deselect the All option and select the option [Children’s Books].

Your output will look like this:

Sure we can select other options from the different columns as well and apply the filter. The list will be filtered on demand and the result could be exactly what you are looking for. But do you have any idea what kind of filter you are applying? I call this the And filter.

And – Or
When filtering lists (data) the basic way you are filtering on an AND type. This means that every filter is applied after the previous one. If we take a look at first image we see the complete list. Now
let’s take a look at another example.  Take a look at the next image. We see a filtered set of data based on the filter we applied. Now what did I ask for? I applied a filter on category AND on kindle? So the list gets filtered first on category and then by ebook availability.

This is what makes filtering difficult.  During my classes I teach my students to listen carefully to what the question actually is. If they have any doubts about that question they should redirect the question and ask for clarification. For example take a look at the first image.

Here’s my question and see if you know how to apply the filter: Show me all books on Travel and all books with a price below 20. What I’m actually asking here is to show me all books on Travel – which also includes a book with a price above 20And all books with a price below 20. So what sounds fairly simple becomes very complex to achieve. We hear one thing but we should apply
another. If we filter first on travel we only see travel books and not all books with a price below 20. If we filter first on price we see all the books with a price below 20 but we do not see all travel books.

So how can we accomplish this?
First I add a couple of empty rows above the first row (about 6). Select the header row, right click and choose insert. Now with Ctrl+Shift+ ‘+’ you can add the other rows. Copy the row header. This should be identical to the existing header so the easiest way would be to copy the header row and past that in the first row. See image below.

Now take a look at the image below. I’ve added two arrows explaining the AndOr functionality. The And functionality goes from left to right and the Or functionality goes from top to bottom. If you want to filter one column after the other you place the criteria on the same row. If you want to filter with multiple criteria in different columns you place the criteria on different rows. Now let’s take a look at the question I asked earlier and how this looks in the query grid.

So again we want to see all books with a price below 20 and all books on travel. This is how we would place the criteria in the additional rows we have just added.

  1. Place your cursor somewhere in the original list (for instance in cell C10);
  2. Now click on the [advanced] button in the Data tab in the ribbon
  3. Excel will show a dialog and place a selection border around your list. If you did not place you cursor in the original list first you will have to manually select the list range;
  4. Now click once in the field [Criteria range] and after that, with the dialog still open, select the first three rows, in this case we select (A1:E3). Nothing more and nothing less
  5. Now click Ok and see the result we were looking for appear (see image below)

And here’s the result:

If you want to clear the filter just click the [clear] button in the Data tab in the ribbon. (by doing so can you see the style bug?).

NOTE: Do not add empty rows to your criteria selection this will not produce the desired filtered outcome. So select exactly the criteria you specified!
Final Question: can you apply a filter to this question. Show me all books with a price greater than 25 and all computer books and all books available on kindle?

Happy filtering 🙂

Advertisements

One thought on “Advanced Filtering in Microsoft Excel

  1. Sasa Kranjac

    Great post Maurice! This is exactly what students ask very often and once they know a little more about filtering they can accomplish a lot more in their daily work.
    Best regards,
    Sasa

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s