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:
- The original input range.
The Criteria range in this case is two cells, specifying one customer.
- One heading from the List range and the customer.
The Copy To range contains three fields in a new sequence.
- 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.
- 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.
- These criteria are joined by an OR.
You will get records that match either criterion.
- 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.
- Filter will return customers who entirely match one row here.
- 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.