Excel – Using the Advanced Filter (Part 2)
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.