Understanding The Excel Advanced Filter

Background

The Excel Advanced Filter gives you database like functionality and helps you crunch large datasets with ease it can also help you begin to better understand database structure.

This article contains several tutorial videos related to the Advanced Filter, enjoy…

Unlike the Autofilter which only allows you to filter on one element per column, the advanced filter allows you to build up complex search criteria and answer more detailed questions about your data.

It becomes even more powerful when you add VBA into the mix for automating workflows, consolidating spreadsheets and moving data.

I’ve used it to ..

• Perform Extract, Transform and Load (ETL) operations.

  • This could be part of a consolidation system where you’re getting data from different departments, perhaps in different formats, I’ve sometimes written macros that open a spreadsheet containing the data that I want to grab, clean it up a bit so that it conforms to a list structure, meaning the data’s got headers and it’s in a neat rectangle.

    I then import it to my consolidation spreadsheet using the advanced filter with a criteria applied so that only the rows I want come across from the source spreadsheet.

  • Load information to a SQL database by first filtering it through the advanced filter to remove unwanted records (duplicate, erroneous or non relevant data).

• Build reporting systems, where the Excel macro would loop over the unique records for a given column, extracting it’s data to a different tab, this could be used to create (pretty) cost center reports in a management accounting system for example (or enhance the reports by adding extra information to each sub report).

• General Business process automation – removing header information from downloaded reports or removing rows that are zero in a particular column (or whatever value you don’t want).

Getting Started

The spreadsheet that goes with this article, contains a data sheet with modified order data from the Microsoft Northwind fictional business database.

We’re going to perform various “database” operations on this data using the advanced filter.

The Advanced Filter can be found under the Data tab in the “Sort & Filter” section, clicking it gives you the following dialog.

The list range section of the dialog needs to point to a rectangular data range similar to this…

To use the advanced filter, you’ll need a data or list range (see above) with unique headers for each column (If your data doesn’t have headers, then add some).

You’ll also need another area with at least one column for your criteria (if you’re filtering in place) and a separate area with headers only, for your extracted data (if you’re filtering to a different range, i.e. creating a subset of the data).

The example below shows the advanced filter dialog with the list source range and Criteria range filled in (range G1:G2).

In the example below, the criteria range is Q1:Q2 and the extract range is Q5:AD5 (the row highlighted in yellow), extract ranges always only consist of the header row because Excel fills out the data beneath that row when you perform the advanced filter “Copy to another location” operation.

The criteria area must have at least one of the columns that exists in the data range, it can have duplicates of a source data column for “and” type filtering logic e.g. >70 and <93.

The criteria must also have a minimum of two rows, one for the header and a clear row to enter criteria items, it can have an unlimited number of criteria rows depending on the complexity of your query e.g. give me Seattle, Boise and Miami in my extract report (under the “Ship City” column, if it were used as a criteria).

First some peculiarities of the Advanced Filter

The Excel advanced filter can be used to move and/or filter a list or tabular data source in place, to another part of the same sheet or tab, to a different sheet or tab in the same workbook or to an entirely different workbook (this last use case can be very useful when consolidating data from other spreadsheets).

The behaviour of the advanced filter varies slightly depending on which of the above extract types you’re doing.

Filtering In Place

Excel will tend to remember the address of the list data range and criteria range provided both are on the same sheet (tab).

Meaning for repeat uses of the Advanced Filter dialog it will auto-populate those sections of the form.

Filter Extract (copy to another location) on same sheet

Just like filtering in place above Excel will remember the address for the data range, criteria range and the extract range.

Filter Extract (copy to another location) on same sheet

Just like filtering in place above Excel will remember the address for the data range, criteria range and the extract range.

Filter Extract to a different tab (sheet) in the same workbook.

This is where using macros can really help, because now you have to start to paint the ranges each time.

If you place the “Criteria” range on the same sheet as the extract range (my recommendation) Excel range names those areas Criteria and Extract respectively.

It remembers the Criteria and Extract ranges but loses the data range, meaning you have to paint it again or just type in or copy in the address to the dialog box.

When performing an Extract to a sheet other than the data sheet, the extract sheet must have focus prior to clicking on the Advanced Filter menu, otherwise you’ll get the error message… "you can only copy filtered data to the active sheet".

Filter Extract to Different Workbook

This is useful for grabbing subsets of large datasets that exist in external files, it has the same minor issue as above, you need to add the source data range each time you do the filter, again this is where using a macro can save you a ton of keystrokes.

Advanced Filter – Examples using modified Northwind Orders dataset.

When I’m giving instructions, note that they’re for the downloadable spreadsheet that goes with this tutorial, if you’re using this article and don’t want to download the accompanying workbook and prefer to work with your own spreadsheet, then please make appropriate adjustments for your own workflow.

Let’s now look at some examples using the advanced filter to grab some useful information from this data.

Orders not yet shipped

Features used :-

• Find rows with blank cells
• multiple criteria items
• Summarizing the extract range (report) with formulas (I can’t bring myself to say formulae, really I can’t…)

So I want to know how many orders have been received, that we have not yet shipped out to customers, this could negatively affect your reputation if there is a holdup on order fulfillment.

Orders not yet shipped can be identified by an empty cell in the “Shipped Date” column (column I) so in order to get this report you need to filter for blanks in the “Shipped Date” items.

So the criteria need only contain one cell i.e. Header is “Shipped Date” and the criteria contains ‘= (single quote followed by =), see zoomed in criteria in image below .

Ok so lets pretend we’re back in 23 June 2006 (I don’t want to have to adjust all those dates – especially since I’ve already created most of the images 😉 ).

From the above report you can see a potential problem, if the date of the report was June 23 2006 (best case scenario), some orders are outstanding for 2 months and others for three months (problem being they need to get with the program and get those deliveries out faster).

Lets modify the query to find orders that are over a month late (ignoring the more recent orders), using the same data we can modify the criteria by adding in the “order date” header to the criteria in cell R1 and specifying less than (<) a given date 30 days ago or whatever.

This gives us a report for orders placed more than a month ago that have not yet been shipped.

I’ve also added a formula in cells AA2 and AD2 to sum down to the end of the spreadsheet and this shows us that we have 9 outstanding orders for a total of 16,074.25 dollars or pounds.

Order qty by customer

Note you can get similar results via a pivot table, but there may be times when a pivot table doesn’t work for what you’re trying to achieve.

Features Used :- Convert data range to table, Extract to different sheet (tab) within same spreadsheet, get unique records for customer i.e. a distinct list of customers from the data, use sumif function with data table to get order quantity and amount for those unique customers.

1. Select data range and press Ctrl + T to convert to table.

   

   Then rename the table from Table2 (in this instance) to tblData.

2. Now perform the data extract.

   1. Click on the “Order Qty Customer” tab.

   2. Click on Data in the Ribbon menu, then Advanced (in the “sort & filter” area), then copy to another location, enter the criteria range (A1:A2) and extract range (A5:E5), then click in the List Range part, then click the data tab and highlight you data source range (tblData that you just created).

      Prior to extracting your setup should look like this, note that “Unique records only” is also ticked.

      

      Click “OK” to perform the extract and a unique list of customers is then extracted.

      

      The right side of the image contains two headers for the "Qty Ordered" and the "Order Value". Here we can use the SumIF formula combined with having converted the data range to table to quickly add summaries to this extract.

      Following formulas are added under “Qty Ordered” and “Order Value” respectively.

      =SUMIF(tblData[Ship Name],'Order Qty By Customer'!$B6,tblData[Qty]) 
      =SUMIF(tblData[Ship Name],'Order Qty By Customer'!$B6,tblData[Extended Price])
      

      Note that the use of tables, means that we only have to address the column header names in the formula and everything else is handled automatically.

      In the example below if you look at the formula you’ll see tblData[Ship Name] being the ship name column rather than having to put in the range address of that column, which makes formula construction much easier.

      

      Note if this was a report I wanted on a regular basis I’d automate this with a macro.

   All Florida orders to a separate workbook

   As with all the other queries above the source data is located on the “Data” sheet of the “advanced-filter-followup.xlsm” workbook.

   I’ve given the data range the range name “Database”, with that in mind to perform the extract without macro support I created the criteria section in the target workbook (it can be placed anywhere) along with the extract headers.

   So you can either paint the source range with your cursor or type or copy it into the “List range” section of the advanced filter dialog box.

   The source range has the following format ‘[workbook name]sheetName’!rangeName or address so in this instance you enter the following to the “List range” area of the dialog box, if you’ve downloaded the accompanying workbook ‘[advanced-filter-followup.xlsm]Data’!Database.

   When the criteria and copy areas are filled out like the example below then click OK and the data subset will be filtered to your new sheet.

   
   

   Wanna grab me a coffee? – Then Click This Green Box

   If you find this article or videos really helpful, then consider grabbing me a coffee, it takes a lot of work and expense to keep this site going, the more caffine I have the harder I can work.

   And if you do grab me a coffee (just click anywhere on this green area), thanks for your support 😉