The Excel 2010 Advanced Filter adds database functionality to Excel, you can query by example and style custom reports with this feature.
Excel Advanced Filter Data Structure
To use any of Excel’s data features, the data that you wish to query must look like a database table, that is to say that it must have a header for each column, it must also have no blank cells in the header, and there must be no blank columns within the range.
In other words it should look like a rectangle with the first row being the column headers (highlighted in dark blue above, A1:D1).
Advanced Filter Data Layout
In order to query this data we are going to use three regions
Region 1
This is where the data is stored and is the source of all your queries i.e. the “database”.
This is the “list range” in the advanced filter dialog box above.
Region 2
This is known as your criteria range, it consists of a minimum of two rows, Row 1 being the header and row 2 being the area where you place examples or exact matches of the data you want to find, such as “Beverages” for the “CategoryName” field (Column name) in cell B1 in the advanced filter data structure picture above.
For this criteria section, you only need to include the column names that you wish to filter on (i.e. place some kind of restriction, such as only show beverages).
However it is OK to just copy the header from your data range and paste it as the criteria header, it might even make things less error prone.
Note you are not restricted to just using two rows for your criteria, if your query is more complex you can have an unlimited number of rows in your criteria, this gives your full “AND” and “OR” query logic (see next section).
AND and OR boolean logic explained
As in show me lastnames of customers who visited “San Francisco” OR “London”, this will return data for people who visited either city.
Then show me results for customers who visited “San Francisco” AND “London” here the results are much smaller as the result will only include people who visited both cities.
Region 3
This is the extract range, where the results of your query will be placed. This contains only the column headers that you want in your “report” (Usually a subset of the data region).
Columns can be in a different order to the original and can be repeated if required (Useful for certain types of reports).
Best practice would be to assign a Range Name to each of the above items. I tend to range name Region 1 as “Database”, Region 2 as “Criteria” and Region 3 as “Extract”.
Now type CategoryName into cell G1 and Condiments into cell G2 (G1 to G2 having been given the range name “criteria”) and copy the header as per this example (or retype it) to cell J3 through M3 (J3 to M3 having been given the range name “extract”).
Type =K4 into cell K1 and =sum(M4:M60000) into cell M1.
This will give you the Category name for each extract and its total value.
Now assuming you have range named the complete data range (A1 to D78 in this example) as “database”, you just need to click the advanced filter command in the “Sort and Filter” area of the “Data” menu (Excel 2007 onwards).
On the popup box you should just need to click “Copy to another location” as the location boxes should be pre filled out (Excel looks for the range names Database, Criteria and Extract by default).
Click OK and the data will be extracted.
Although on it’s own this advanced filter process may seem to have limited usefulness over the autofilter, it’s power comes from the fact that the number of criteria rows can be unlimited (allowing complex “OR” queries) and column names can also be duplicated (allowing complex “AND” queries).
With this functionality added to a vba macro you can acccomplish huge complex data processing and reporting tasks very quickly and elegantly.
Please comment below so that I can continue to improve the quality of the information contained in these posts.
Thank you so much. This is FAR more helpful than the MS website!
It would be helpful if you included an example that can be downloaded to excel so users can try it themselves.
Hi Emily
Thanks for your comment, maybe in the future I will get around to doing that however in the meantime if you have any specific questions please feel free to post them in the comments and I will do my best to answer them.
Hi Sean,
In VBA how can you find out how many visible rows there are after applying the Advanced Filter?…I can do it with AutoFilter but can’t seem to find out how to do it with AdvancedFilters.
Many thanks in advance
Adrian
Hi Adrian
The point of using the advanced filter with vba is to extract the matching records to a new area of the the spreadsheet or to a different spreadsheet or workbook. You can then query this result set with code which gets a reference to the current region of the extracted data.
I would then use offset and resize to query that data. So for example with the region 3 image above you could use code like this…
dim rngResult as range
dim iRows as integer, iCols as integer
set rngResult=Sheet1.range("j3").currentregion ' Assuming worksheet is Sheet1 And Categoryid is on row 3 as per region 3 image.
set rngresult=rngResult.resize(rngResult.rows.count-1).offset(1)
iRows=rngResult.rows.count
iColumns=rngResult.columns.count
As you can see with the above snippit if you use the advanced filter with the “copy to other region” option, then visible rows are not part of the paradigm, thats for the autofilter. The advanced filter is more about generating a report from the data list (or multiple reports) its not about making display changes to the original list (which you would keep as the master list).
I am a novice at using VBA code. However, I’m learning. I have been successful in using the Advanced Filter to select and transfer selected date to a new sheet. I have a 58 sheet workbook. Can I use Avanced Filter in VBA code to run the filter on every sheet in the work book adding the selcted data to the same sheet? If so what might tht code look like? The VBA code below will copy thge data from Sheets 1 & 2 to Sheet 4. But I need to filter it first.
Thank you.
Sub submit()
Dim Counter, Counter2, Counter3 As Integer
Counter = 2
Counter2 = 2
Counter3 = 2
'This code finds the bottom of sheet4
Do Until ThisWorkbook.Sheets("sheet4").Cells(Counter3, 1).Value = ""
Counter3 = Counter3 + 1
Loop
'This code assigns values to sheet4 from sheet1 & Sheet2
Do Until ThisWorkbook.Sheets("sheet1").Cells(Counter, 1).Value = ""
ThisWorkbook.Sheets("sheet4").Range("A" & Counter3, "D" & Counter3).Value = ThisWorkbook.Sheets("sheet1").Range("A" & Counter, "D" & Counter).Value
Counter3 = Counter3 + 1
ThisWorkbook.Sheets("sheet4").Range("A" & Counter3, "D" & Counter3).Value = ThisWorkbook.Sheets("sheet2").Range("A" & Counter2, "D" & Counter2).Value
Counter3 = Counter3 + 1
Counter = Counter + 1
Loop
End Sub
How can I clear the filter conditions, without getting rid of the filter itself? Is that even possible. I understand needing to create a MACRO to do it quicker, but I don’t know how to do it at all. The only thing that I seem to be able to do is to clear the entire filter when I try to clear the conditions. Any help would be very much appreciated.
Hi Katie
In the example that I use in this article, the filter conditions are in cells G1:G2 i.e “Category Name” and “condiments”. So here we are using the advanced filter functionality as opposed to Autofilter, and using the “Copy to another location option” rather than “filter the list in place”.
So this means that the filtered data is a subset of the original data that has been copied to another worksheet or workbook. The original data is still where it was unfiltered if you wish to refer to it.
To Clear the filter just delete the line condiments and just choose Advanced Filter and OK again. Or to not lose your original filter conditions you can copy them to another part of the worksheet (cells G1 to G2 in this example) and change the field name or value or both in cells G1 and G2 and filter again.
Clearing the filter is not part of this paradigm because you are not filtering the original data when you do it this way, you are extracting subsets of it to different locations.
Thanks and appreciated for the simple and clear explanation of Advanced Filters (really extract).
Question: you mentioned “and” and “or” conditions – how are these actually applied?
just a guess perhaps with > < = etc.???
Hi Robli
In the criteria section “G1 to G2” in this example, You get an “OR” Condition if you changed the criteria to “G1 to G3” and then typed “Beverages” into G3. You get an AND condition if you change the criteria to G1 to H2 and in H1 type “ProductName” for example and in H2 type “Aniseed*”.
Run your Advanced Filter again and now you have a more limited result which is CategoryName=Condiments AND ProductName must begin with “Aniseed”.
Hope this helps