This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Use Advanced Filter
Problem: What is the advanced filter used for?
Strategy: As the AutoFilter Filter gets more features, there are less times that you need to switch over to the Advanced Filter. There are still a few tasks that might be easier with the Advanced Filter.
The Advanced Filter can be used to filter to a subset of columns and/or to re-order columns. In the previous example, you filtered to a single column of customer. When you use the Copy To Another Location option and put headings in the Copy To range, you are specifying which columns and the order of the columns. In the next three figures, you will see the List range, the Criteria range, and the Copy To range of a filter designed to produce a report for General Motors.
The List range contains seven fields:
Figure 634 The original input range.
The Criteria range in this case is two cells, specifying one customer.
Figure 635 One heading from the List range and the customer.
The Copy To range contains three fields in a new sequence.
Figure 636 Specify the fields to be returned.
Select one cell in the input range and choose Advanced Filter. Select Copy To Another Location. Fill in the three ranges. Do not check the Unique Values Only box.
Figure 637 Filter three columns for one customer.
The result is a new report with three columns of purchases by one customer.
You can use Advanced Filter to create some unusual criteria ranges where the criteria are joined by a logical OR. Say that you were looking for customers in the Transportation market or and customers who purchased product S109. Set up a criteria range with each criterion on a different row.
Figure 638 These criteria are joined by an OR.
You will get records that match either criterion.
Figure 639 Records that match either criterion.
Criteria entered on the same row are joined by AND. In this example, you are looking for records where a Transportation customer purchased S102, or where a Retail customer purchased S109, or where a Financial customer purchased S108, or the purchase of S110 by any industry.
Figure 640 Filter will return customers who entirely match one row here.
Figure 641 Results of the filter.
Additional Details: To clear the advanced filter and show all the rows again, choose Data, Clear.
Several versions ago, you were not able to specify a Copy To range on a different worksheet than the List range. This limitation has been lifted.