Excel Advanced Filter

Excel supports two different ways to filter data that are in tabular format. Autofilter is a built-in capability driven via the user interface. As sophisticated as Autofilter has become in recent versions of Excel, no pre-defined setup can possibly cater to all the different questions that the consumer may want answered. These require a custom filter and Advanced Filter provides that capability. It is a data-driven mechanism that uses Excel formulas to extract specific information from the original data. For those who may have heard of SQL but have never been motivated to learn it, you can now leverage some of the power of SQL without learning a single word of SQL!

The layout of this document is as follows: 1) Introduction to the data set used in the examples, 2) Introduction to the Advanced Filter dialog box, 3) Filter using column headers, 4) Filter using Excel formulas, 5) Extract unique data, 6) Work with dynamic source data, and 7) Create a filter in a different worksheet or workbook.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/data_analysis/06.shtml

Tushar Mehta

9 thoughts on “Excel Advanced Filter

  1. Walt, if I may.

    The best I remember, this was displayed on Contextures. I don’t remember the date, but seems like it was within the last year.

    I do this kinda regular, but not often enough to have it memorized.

    Criteria and Extract ranges on one sheet (seems like extract has to have more than one row). Anyway, you initiate the Advanced Filter from that sheet and modify ranges accordingly.

  2. Thank you for an extremely useful post. I have extended it by using a macro to run the Advanced Filter, where I want to see the same data for different criteria. Change what I am after and click a button that fills and runs the Advanced Filter.

  3. Thank you for an extremely useful post, Excellent refresher tutorial, gave me some ideas

  4. Myself, I’d live with the overhead of using MS Query rather than advanced filters. Excel’s advanced filters have been second rate at best since 1989 when Lotus 1-2-3 Release 3 came out but MSFT decided not to update Excel’s 1-2-3 Release 2-like functionality.

  5. …but MS Query is not without its issues:
    1. Could have issues retrieving data from the same workbook its located in (although the memory leak issue appears to be mainly fixed in Excel 2007/10)
    2. Users have to be made aware that if they save the workbook somewhere else, the query will continue to reference the old workbook. Requires some VBA to ‘failsafe’ this

    So if I would choose advanced filters over MS Query if I was designing something for non-experts to use. That said, I’d choose ADO over both.

    THere is a great article at http://support.microsoft.com/kb/282851 about combining Data Tables with Advanced Filters and Excel’s Database formulas (e.g. DSUM etc) . You can use datatables to ‘swap out’ the criteria in a DSUM or any other database function. Very very powerful indeed.
    So you can set up amazing filtering run directly off the spreadsheet in a way that users can easily interact with, and do stuff that would otherwise require SQL. Blows pivots out of the water, on account that pivots only allow additive filtering. (Of course, you could always feed pivots with disconnected recordsets and generate the requisitie SQL on the fly, but that could get messy)


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.