Advanced filtering to another location.

 

On the “Data” sheet, we have a data area (1) consisting of 977 rows with the following format: “Customer Name, Order Date, Product, Quantity, Unit Price, Excl.VAT, Incl. VAT”.

We are going to look at the advanced filtering on the same sheet(with 1 or more criteria) and moving an advanced filter to another sheet.

Advanced filter on the same sheet (with a single criterion).
We first define the filter criteria, eg., we want to filter by customer name.
Copy the contents of cell A1, which is “Customer name” and paste it in cell I1.
Type or copy a customer name in cell I2, eg. SeaPak.
Select and copy the cells B1 to G1.
We right-click in cell I4 and select “Paste” because the “Customer Name” is already in cell I1.

Tip: The column titles that we use for criteria (1) should not be added to the filter result (2). We do know (3) that we should take this into account when we use “Copy to”.

Click in cell I4.
Click the “Data” (1) tab in the “Ribbon” and click on “Advanced” (2).

In the “Advanced Filter” we see that the Excel data area (List Range) (1) has been selected.
Click “Copy to another location” (2).
Click in the “Criteria range” (3) box and select the cells I1 and I2.
Click in the “Copy to:” (4) box, select cells I4 to N4 and click OK.

Advanced filter in the same sheet (with multiple criteria).
We can also use the “Advanced Filter” with multiple criteria.
Some examples:
Using “AND” criteria, you can filter based on the “Customer name” (SeaPak) and “Quantity” (> 10).
(Comparison operators that can be used are: = is equal to, > greater than, > = greater than or equal to, < less than, not equal to).

Using “AND” criteria, you can filter based on the”Customer name” (SeaPak), the “Order Date” (29/02/2008) and the “Product” (XYZ-11120).

Filter criteria between 2 dates.

Using “AND” criteria, we filter the “Quantity” between 2 values.

Using “AND” criteria, we filter both “Customer name” (SeaPak) and “Quantity” between 2 values.

Using “AND” and “OR” criteria, we filter the “Customer name” and “Order Date” or the “Order Date” and “Product”.

Filter with a (*) wildcard.
The asterisk (*), as a wildcard, stands for any number of characters in that position, including zero characters.
We filter the “Customer Name” on all names containing the word “Milk” and preceded or followed by other characters including a space.

Filtering the “Customer name” starting with the letter “C”

Filtering with a (?) wildcard.
The question mark (?) which is a wildcard character, represents a character or characters in that position. We filter the “Customer name” starting with the letter “A” followed by any character and then followed by the letter “C”.

Combinations of comparison operators and wildcards are also possible:
“Customer names” not starting with the letter A.

“Customer names” not ending with the letter A

There are other possible combinations but, they are too many to mention.

Advanced Filter to another sheet.
If we open the “Advanced Filter” dialog box in the “Data” sheet and insert a reference to “Sheet1” in “Copy to” and then click OK, Excel displays a warning (1) as shown below.

If we want advanced filtering on a different sheet, we open the “Advanced Filter” dialog box calls from the sheet where we want to insert the filtered data (the sheet should be “active” when we apply the filter).
Select the “Data” sheet, select and copy cell A1.
Click on the “Sheet1” tab and right-click in a cell, eg. B1 and, select “Paste”.
Type a name in cell B2 (Or copy a name from column A of the “Data” sheet and paste it into cell B2 of “Sheet1”)
Click on the “Data” tab in the “Ribbon”(1) and click on “Advanced” (2).

In the “Advanced Filter” dialog box, select “Copy to another location” (1).
Click in the “List range” (2), click on the “Data” sheet tab.

Click in cell A1, Press Ctrl + Shift + (Right arrow), and then with the Ctrl + Shift + (Down arrow) key combination, the cells A1 to G977 are selected.

Click in the “Criteria range” text box, and select the cells B1 and B2.
Click in the “Copy to” text box, click in cell B5 and click OK.

Now the filtered data is moved to “Sheet1”.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats