Advanced Filter in Excel

The advanced filter is different from the auto filter in Excel. This feature is not like a button that one can use with a single click of the mouse. To use an advanced filter, we have to define criteria for the auto filter and then click on the "Data" tab. Then, in the advanced section for the advanced filter, we will fill our criteria for the data.

For example, suppose you have a dataset. In this dataset, there are duplicate records. That could be due to an error in the data entry or a data compilation. We can use Excel Advanced Filter Tool to receive unique records in other locations, retaining the original data.

Let us learn the use of this with some examples.

Example #1

Suppose we have the following data to filter based on different criteria:

We need to check the sales transaction made by “Taran” and “Suresh.” Then, we can use the OR operator, displaying the records satisfying any conditions. Then, we can follow the steps to apply these filters in Excel to get the results.

There are two actions in an advanced filter.

• Filter the list in place: This option filters the list at the original position, i.e., on the "List Range."After analyzing, we can remove the filter using the "Clear" command in the "Sort & Filter" group under "Data."

• Copy to another location: This option copies the desired data according to the criteria to the specified range.

We can use any option according to our needs, but we will use the 2nd option more often.

Now, we need to:

• First, open the "Advanced Filter" dialog box.

• Specifying the "List Range" as $A$5:$D$26, "Criteria Range" as $A$1:$D$3, and "Copy to" Range as $F$5:$I$26. Click on "OK.

All the records with names 'Suresh' or 'Taran' are filtered out and displayed separately in a different cell range.

Example #2

We want all the sales transactions of Qtr 1 and South India. Therefore, the "Criteria Range" is as below:

As we have the 'AND' condition here, we want to display the records where both conditions are met. Therefore, we have mentioned the criteria below both column headings in the same row.

Now, we will click on the "Advanced" command in the "Sort & Filter" group under the "Data" tab.

From the 'Advanced Filter" dialog box, we will choose "Copy to another location" and then define the A5: D26 as List Range, A1: D2 as "Criteria Range," and F5:I26 as "Copy To" range.

Now the result is as follows:

Example #3

We want to find sales in Qtr 1 or made in North India.

We need to specify both the criteria in different rows and different columns. We have to display the data if any conditions are met, and both are related to different columns.

Steps:

• We need to open the "Advanced Filter" dialog box.

• We will specify "List Range" as $A$5:$D$26.

• Also, specify "Criteria Range" as $A$1:$D$3.

• And, specify the "Copy To" range as $F$5:$I$26.

The result would be as follows:

Example #4

Now, we want to find all sales of ₹2,000 - ₹4,000 and ₹10,000 - ₹13,000.

As we have four conditions,

(Condition 1 AND Condition 2) OR (Condition 3 AND Condition 4).

(>=2000 AND <=4000) OR (>=10000 AND <=13000)

We have mentioned the conditions with "AND" in the same row and "OR" in different rows.

Steps:

• To open the "Advanced Filter" dialog box, we will click on "Advanced" in the "Sort & Filter" group under "Data."

• In the "Advanced Filter" dialog box, we will specify "List Range" as $A$5:$D$26

• And, we will specify the “Criteria Range" as $A$1:$D$3

• Also, "Copy to" Range as $F$5:$I$26

• After clicking on "OK," the result will be:

Example #5

We want to find the sales of Qtr 1 by Sunny or that of Qtr 3 by Mukesh.

As we have AND and OR, both relations in conditions, we will specify the conditions in the criteria range in different rows (OR) and other columns (AND).

Steps:

• To open the "Advanced Filter" dialog box, we will click on "Advanced" in the "Sort & Filter" group under "Data."

• In the "Advanced Filter" dialog box, we specify "List Range" as $A$5:$D$26.

• And we will specify the "Criteria Range" as $A$1:$D$3.

• Also, “Copy to” Range as $F$5:$I$26.

• After clicking on "OK", then the result would be:

Example #6 - Using WILDCARD Characters

We want to find all the sales transactions with a name ending with “esh” or a region's first word ending with “st” and only want to retrieve the name, sales, and region.

Here, * denotes more than one character, and “?” represents only one character.

Before implementing the advanced filter, we need to specify the column labels on "Copy to Range" as we want only some columns, not all.

Now, we will call the command.

Steps:

• To open the ‘Advanced Filter’ dialog box, we will click on ‘Advanced’ in the ‘Sort & Filter’ group under ‘Data.’

• In the "Advanced Filter" dialog box, we specify "List Range" as $A$5:$D$26.

• We will specify the “Criteria Range” as $A$1:$D$3

• Also, “Copy to” Range as $F$5:$H$26

• After clicking on “OK,” then the result would be:

Example #7

We want to filter the top five sales (of a large amount).

The formula cell must evaluate TRUE or FALSE to get the most significant five records. Hence, we used the LARGE Excel function and compared the value with the sales amount.

As we can see, the column heading for the formula cell is blank. Therefore, we can either keep it empty or give it a name that does not match the column headings in the data range.

We will specify the ranges in the "Advanced Filter" dialog box. Steps are:

• To open the "Advanced Filter" dialog box, we will click on "Advanced" in the "Sort & Filter" group under "Data."

• In the "Excel Advanced Filter" dialog box, we will specify "List Range" as $A$5:$D$26.

• And "Criteria Range" as $A$1:$E$2

• Also,"Copy to" Range as $F$5:$I$26

• After clicking on "OK," then, the result would be like this:

• The range it needs to be applied must have a unique heading, as duplicate headers cause problems when running an advanced filter.
• There should be at least one blank row between the "List Range" and "Criteria Range."