Excel 2010 Advanced Filter Tutorial
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.
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.
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.
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.