MrExcel.com - Your One Stop for Excel Tips & Solutions

Use Advanced Filter

September 15, 2022 - by

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:

The original (unfiltered) data is in columns A through G with headings of Region, Product, Market, Date, Customer, Quantity, and Revenue.
Figure 688. The original input range.

The Criteria range in this case is two cells, specifying one customer.

The Criteria range in I1:I2 contains a the Customer heading in I1 and General Motors in I2.
Figure 689. One heading from the List range and the customer.

The Copy To range contains three fields in a new sequence.

The output range in K1:M1 contains three headings from the data set in a new order from the original data set: Date, Product, and Quantity
Figure 690. 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.

In the Advanced Filter dialog, choose Copy to Another Location. The List Range is A1:G567. The Criteria range is I1:I2. The Copy To range is K1:M1. Do not choose Unique Records Only. Click OK.
Figure 691. Filter three columns for one customer.

The result is a new report with three columns of purchases by one customer.

All of the General Motors records from the original data set are copied to column K.
Figure 692. Original data in A:G, Criteria in I, Output range in K:M.

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.

A different criteria range with headings for Market in J1 and Product in K1. The Market in J2 is Transportation. The Product of S109 is in K3. Because S109 and Transportation are on different rows, the criteria is treated as an OR. You will get records that are either Transportation or product S109.
Figure 693. These criteria are joined by an OR.

You will get records that match either criterion.

The results of Filter in Place and the new criteria. Rows 4, 6, and 9 are visible because they are Transportation. Rows 5, 7, 12 are visible because of Product S109. Rows 2, 3, 8, 10 and 11 are hidden by the filter. This particular result would be difficult to achieve with the regular Filter.
Figure 694. 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.

Here is a criteria range with four rows of data. In row 2, you are looking for Transportation AND product S102. In row 3, you are looking for Retail and S109. In row 3, you are looking for Financial and S108. In row 5, no Market is specified, only product S110. This will give you all sales of S110 across all markets.
Figure 695. Filter will return customers who entirely match one row here.

The results of the previous criteria range are shown.
Figure 696. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Yann Allegre on Unsplash