In my other blog about the ADVANCED FILTER I looked at how you set one up and build up your criteria.
Now I am going to take a step further and look at how to create criteria ranges i.e. a between…and… type filter. Everything from the other blog still stands in terms of basic set up and creating a variety of criteria. However, when you want to set a filter based on a range of values you need make a minor change to your criteria table.
Continuing with the example from the previous blog, I want to pick out people in the IT department who earn between 25000 and 28000. If you have used Access or other database systems you would use something like ‘Between 25000 and 28000’ but unfortunately this does not work in Excel.
Apply this and the filter will simply return an empty list. So we need a slightly different tactic. Copy the column heading you are basing your data range on. In this example, I want to select a range of salaries so I will copy the ‘Salary’ heading. You can either insert this to one side of the current column, or, and this is my personal preference, paste it to the end of the heading row of your criteria table.
Now it is just a case of building up your criteria, adding a start value in one cell, and an end value in the copied heading column. So in my example I would enter >25000 in one column, and on the same row I would enter <28000 in my new ‘salary’ column.
Once again, you can make these as complicated as you like, and this is the beauty of the ADVANCED FILTER, you can have multiple criteria row each with different values and ranges.
The filter above would be looking for the following;
- People in IT AND who earn between £25-£28,000 OR…
- People in IT AND who earn between £18-£21,000 OR…
- People in Training AND who earn between £23-£26,000
When you select your criteria range make sure you include the ‘new’ column. Don’t worry that the criteria table is wider than the original table, Excel will be searching for column headings, so as long as they match, no problem. Continue to add as many ‘new’ columns as required.
So that’s how to build range filters, but the ADVANCED FILTER also has an option to pick out UNIQUE values.
When extracting unique values from a list, there are two things to remember
- Only select the column you want to extract the unique values from – not the entire table.
- Ignore the criteria table – you don’t need it.
Let’s say I want to get a list of all the unique job titles:
Open the ADVANCED FILTER and from the dialog box, decide whether you want to filter in place or copy to another location. Again, my personal preference for this is to display the output in another location away from the data table.
Then select the data you want to filter out unique values from. In this case I want all the job titles. IGNORE the criteria range box.
If you selected COPY TO ANOTHER LOCATION then select a cell from where you want to display the filtered list.
Finally, make sure you tick the UNIQUE RECORDS ONLY option. And click on OK.
You will now see a list of unique values…
But not quite…..Notice we have administrator twice. So what happened?
When I selected the job titles I highlighted only the job titles…which would seem like a logical thing to do, but Excel treats the first value in the selected data as the column heading. SO in this case the first line contains ‘administrator’ so assumes this is a heading and displays it at the top of the list. To avoid this, simply include the column heading when you select your range and then you will get the correct answer;
And there you have it….the ADVANCED FILTER.
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.