Level 2 - Lesson 24 - Advanced Filter
Category: [General,Excel] Demo Available
Advanced Filter
The Advanced Filter is used when you need to either filter your data down in a way that the regular Filter cannot (like with using calculations) and/or you want the result of your filtered data to be copied to another area on the Worksheet. The Advanced Filter dialog can be found by going in the same location as the Filter by going to Sort & Filter options under the Data tab and selecting Advanced.
You will see the option to either Filter the list, in place or Copy to another location. If you opt to Filter the list, in place, your table or list (list range) will be filtered in the same way as the Filter would do so. In other words it will hide all rows that do not meet the criteria as specified in the Criteria range: If you opt to Copy to another location your list range that meets the criteria as specified in the Criteria range will be copied to another location on the same Worksheet. The Copy to another location option does not allow you to copy your results to another Worksheet! If you do wish to do this simply opt to Copy to another location on the same Worksheet, then cut and paste the results to another Worksheet.
You may remember that point 2 in Table and List Guidelines was:
Leave at least three blank rows above your headings. These can be used for formulas, critical data etc. You can hide the rows if you wish.
This is so we can use them for setting a Criteria for an Advanced Filter. When you set a Criteria you refer to it using the Criteria range box. Your criteria range should include the column headings of your list or table. So we simply copy our column headings to the top blank row. It is below these headings that we put our criteria.
Using the list of names example, we would have a copy of our heading Names in, say cell A1.
You could type John in cell A2 and Dave in cell A3.
Select any cell within your table and go to Sort & Filter options under the Data tab and selecting Advanced
Select Filter the list in place (it should be the default
Make sure your list range is correct.
Put A1:A3 in your Criteria range box
Click OK
Our table would then only display the information for John and Dave. To add more names we would insert some more rows above our data and use these to add the extra names. However, we believe it is better to have the extra rows there to begin with, and as we have said previously, leave at least three rows above your data and hide them. Note that there are no drop-down arrows to the right of your list headings, but the row numbers have gone blue as in the case of Filters.
We could, of course, do exactly the same and use the Copy to another location option, the only thing we would need to do is place a cell reference in the Copy to: box to start our new table in.
We could also extend our criteria to not only show John and Dave's information, but to show only John and Dave IF they are older than age 24. To do this we would put: >24 in the cell directly below our copied heading Age and >24 again in the cell below this. Then apply the Advanced Filter and include the new criteria in the Criteria box ie; A1:D3, where our new criteria for the age is in cells D1:D3. It does not matter that there is no criteria in cells B1:C3 as blank cells would be ignored.
We can also use formulas as our criteria when using Advanced Filter, which can make our filtering capabilities even more complex. However any formula we use:
must evaluate to either TRUE or FALSE.
The formula we use, must use a relative cell reference to the first data cell in column we want to filter
The range within the formula itself must be an absolute reference.
If we do not apply these three rules the Advanced Filter will not work as expected.
So let's say we have the column heading Pay Rate in cell B6 and we wanted to find the information on the person who has the third largest pay rate. To do this we could use the formula: =B7=LARGE($B$7:$B$27,3) place it in cell B2. Where B7 is our first Pay Rate and $B$7:$B$27 is the entire range below Pay Rate.
When we use the Advanced Filter this time we must not have our column heading Pay Rate in cell B1. We would still refer our Criteria range in the Advanced Filter dialog to: $B$1:$B$2 but we cannot have a column heading in B1. In other words we use the formula cell and a blank cell above it. We can place data in cell B1 but it cannot be a column heading from our table.
We have included a Workbook example of Advanced Filters to give you an idea on how they can be used. There is also some good information in the Excel help under: Filter a list using advanced criteria that is worth reading. As with the use of most formulas in Excel it can take some trial and error before we get the result we want. But as we have said before "making mistakes is a great way to learn".
The other very handy feature of the Advanced Filter is that we can use it to create a list of Unique records only. To do this we simply need to check the Unique record only box and leave the Criteria range box blank. When we use this option though we can only use include a single column list range.
It is also possible to create a list of Unique record only that also meet a criteria we have set. To do this we simply refer the Criteria range to a range that contains a criteria.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets and Index to Excel VBA Level 1 Free Lessons and
Index to Excel Level 2 Lessons and Index to COVID-19 Charting examples