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
This is one of the best, most easily understood, detailed articles on how to do something in Excel that I have ever read. I have learned a great deal. The only thing I am puzzled about is why I am unable to get the filter to automatically refresh the output once I refresh the data in the source. Is that not a capability of this function?
Hi Mary, Sorry for the delay on your comment. I am catching up on blog responses.
Thank you for your comment. Much appreciated.
What you point out is a built-in limitation of the function. For reasons best know to itself, Microsoft opted to not automatically refresh advanced filters when the underlying data change.