Sep 04, 2014

Filtering is a common task in Microsoft Excel and the normal filtering (the ‘autofilter’) can do quite a lot of what you will need. However, there are some filters that even the normal filtering can’t do. This is where you can use an Advanced Filter. By the way, ‘Advanced Filter’ means that it can do very complicated filters but doesn’t mean that it is complicated or hard to set up. In some ways, an Advanced Filter is easier to use than a normal filter. To set up an Advanced Filter, first you need to set up a filter Criteria Area. This has the same headings as the area you want to filter. The easiest way to do this is to copy and paste the heading row of your data. It is easiest to put the Criteria Area above (or below) the area you are filtering, because when you filter, you hide the whole row and having half of your Criteria Area hidden makes it harder to see what is going on. So, in this example file, I have copy and pasted the part of the heading row that includes the columns I want to filter on from Row 6 (E6:H6) and into J1:M1.

Why am I copying and pasting? I’m doing this because the column heading names in the criteria area have to be exactly the same as those in the data area, otherwise the Advanced Filter won’t marry them up. Now, I use the area underneath the headings in my Criteria Area to put in the things I want to filter for. For example, if I want to find all the ‘Maine’ ‘Sales’ people as well as the ‘Vermont’ ‘Staff,’ I would proceed with the below. A normal filter could NOT do this particular filter. To begin, I’ll put this in the Criteria Area:

To perform the actual filtering, click in the area you want to filter (not the Criteria area) and then go: Data – Sort & Filter – Advanced:

You will then come to this menu:

The top box should have the area of data you want to filter already in it. (You can select it manually if it doesn’t.) Then click in the Criteria range input area and then select the Criteria area we just set up, including the rows with the actual criteria (Maine and Sales and so on). In this example, I1:M3

Extra Option: There is an option to take the result and ‘Copy to another location’. So instead of just hiding the rows you don’t want to see and filtering the list in-place, you could have the result rows that the filter finds pasted onto another sheet. Given you can always copy and paste, I don’t use the ‘Copy to another location’ very often. When you click on OK, the filter will do its job.

Note: A blank in the Criteria area means ‘anything’. Also, realise that each row of your Criteria area is an ‘AND.’ A logical AND means you have to meet all the criteria, so in this case DIVISION has to be ‘Maine’ and DEPT has to be ‘Sales’, DATE OF HIRE can be anything, and GROSS PAY can be anything. Between the rows of the Criteria area is an ‘OR.’ A logical OR means you can meet either (any one) of the criteria. So for the filter to show a row of data, that data has to meet either all of the first row or all of the second row. To clear off an Advanced Filter, click on the Clear button in the data tab.  To do an example with number columns, I put the following text into cell M4:  >800 When I perform the Advanced Filter again I have to be careful to change the Criteria area to include row 4. The menu looks like this: This will find three groups of people:

1. Maine Sales regardless of what they earn
2. Vermont Staff regardless of what they earn
3. People who earn over $800 regardless of what division or dept they work in.

If I’d wanted to find Main Sales and Vermont Staff who earn over $800, I would have had to have the Criteria area looking like this:

One last thing about the Criteria area, don’t have an entirely blank row in your Criteria area, because the Advanced Filter looks at that row and thinks ‘anything’, ‘anything’, ‘anything’, ‘anything.’ Every piece of data meets that super easy criteria so it shows every row. The cool thing about Advanced Filters is that because the Criteria area is text in cells it gets saved as part of the file, making it quicker, in some cases, to apply an existing Advanced Filter than a normal filter. Hope that helps you with your filtering in Excel.