You are probably used to using AUTOFILTER and in the more recent versions of Excel, this has improved a lot with the option to filter or sort by colour or conditional format icon as well as give you a number of pre-set filter conditions that are data sensitive, so the wording of the filter conditions change depending on whether you are looking at dates, numbers or text.
Using AUTOFILTER you can build up some fairly clever filters but there are limitations. With each new filter you apply you are creating an AND type filter where ALL filter conditions have to be met. Yes, you can add in an OR condition within each filter, but you are limited to just the one per column filter.
For example, I want to filter out staff in either the IT or Training departments – easy enough. I then want to filter out people whose salaries fall between certain values. Again simple enough, but it applies the salary range to BOTH of the selected departments. What if I want to see people in IT who fall in one salary range and trainers who fall in a different salary range? I could run two separate filters but I want to see this in one table, not two separate outputs.
This is where your ADVANCED FILTER comes in.
First of all copy all your headings and paste them somewhere on the same worksheet. Personally, I prefer to paste them a few rows below my data table. The rows below your copied headings are where you are going to build the criteria for your ADVANCED FILTER.
Now type in your criteria: under each copied heading type text, dates numbers etc. which are the values you want to filter out. So, taking our example above again, I would type in IT in one cell and Training in the row directly below that.
Similar to our AUTOFILTER, each column I type a value into, equates to an AND condition. Values entered on subsequent rows represent OR. So by typing IT in one row, and Training in the next row I am creating an OR type filter – show me people in either IT OR Training.
Once your criteria are set up, it’s time to run the ADVANCED FILTER. Click back into your data table – this is a MUST. Go to the DATA tab, and click on the ADVANCED button in the SORT & FILTER group.
This will open the following window;
Decide whether you want to apply the filter directly to the data table or to show it as a separate output elsewhere in the SAME worksheet. Personally I prefer to do it in place, but your choice. Excel should automatically work out the LIST RANGE (that’s why you click into your data table before running the filter). Then select your CRITERIA RANGE i.e. the values and the column labels (you MUST include the column labels here) from the table you pasted below your data list. You only need to select the columns that contain criteria, although selecting the entire table will not cause any problems.
Click on OK, your table will now only display the filtered information.
So if I want to extend my filter to include salaries then I can add criteria in the SALARY column of my criteria table. Now, this is where you need to be a bit careful. If I enter a criteria such as <25000 on the same line as the IT department cell but don’t put anything against Training, it will filter anyone in IT with a salary under 25000 but will display all salaries for training as no criteria have been applied. By writing this I would be looking to filter the following information:
- People in IT AND who have salaries under 25000
- OR people in Training
If I want to set a salary criteria for the Training department then I would need to add something on that row too. This is where the possibilities start to dramatically increase with the ADVANCED FILTER because unlike the standard AUTOFILTER I can apply different criteria against each item filtered, or even multiple criteria.
In this example I am filtering for:
- People in IT with a salary greater than 25000 OR…
- People in Training who do not have the job title of “Trainer” OR…
- People in Finance who’s surname begins with “P”
So in your criteria you can use all the normal operators (>, <, >=, <=, <>) as well as wildcards (*, ?). The possibilities are endless!
A word of caution though. If you apply a filter and yet you still see the entire table, either your filter is not precise enough or, most likely, you have included a blank line in your CRITERIA RANGE. A blank line basically says show me data with no special criteria – in other words show me everything. If that happens, just modify the CRITERIA RANGE references and re-run the filter.
In another blog I’ll show you how to create filter RANGES as well as how to extract unique values out of a list using the ADVANCED FILTER.