Excel Advanced Filter | Step by Step Guide to use Advanced Filter in Excel

Excel Functions Tutorials

Table of Contents

Excel Advanced Filter
Importance of Advanced Filter in Excel
How to Use Advanced Filter in Excel? (With Examples)

What is Advanced Filter in Excel?

Filtering data simply implies seeing limited data. The remaining data that we do not want to see is not deleted or does not change in any way, it just gets hidden. If we have a thousand rows of data, it will become difficult for us to analyze the data or locating the data, which we want to see and understand anything from the data. That is why we apply the advanced filter in excel to get the only subset of data that we desire.

How to Use Advanced Filter in Excel? (With Examples)

Let us learn the use of Excel Advanced Filters by some examples.

Example #1 – Advanced Filter in Excel with OR Function

Suppose we have, following data to filter based on different criteria.

We need to check sales transaction which was made by ‘Taran’ and ‘Suresh’, then we can use the OR operator which displays the records satisfying any of the condition. To get the results we can apply advanced filters in Excel.

To apply an excel advanced filter, we need to first select any of the cells in the data range

then click on Data tab-> Sort & Filter group -> Advanced command

As we click on ‘Excel Advanced’, a dialog box ‘Advanced Filter’ will open for asking List Range to filter, Criteria Range for defining the criteria and Extract Range for copying the filtered data (if desired).

For Criteria, we need to copy the column headings on the top row and define the criteria below field heading.

To specify the criteria, we can use the comparison operator, which are as follows:

As we want to get all records having name ‘Suresh’ or ‘Taran’. The Criteria Range would be like below:

For ‘OR’ conditions where we want to display the records which satisfy any of the condition then we need to specify the criteria in different rows.

There are two actions in excel advanced filter.

Filter the list, in place: This option filters the list at the original place i.e. on list range itself and after analysing, we can remove the filter using ‘Clear’ command in ‘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 of the options according to our need but we will be using the 2^nd option more often.

Now we just need to

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’.

We can see all the records having Name as ‘Suresh’ or ‘Taran’ are filtered out and displaying separately in a different cell range.

Example #2 – Advanced Filter in Excel Using the AND Criteria

Now we want to get the all the sales transaction of Qtr 1 and South India. Criteria Range is as below:

As we have here ‘AND’ condition i.e., we want to display the records where both the conditions are met that is why we have mentioned the criteria below both column headings in the same row.

Now we will click on ‘Advanced’ command in ‘Sort & Filter’ group under ‘Data’ tab.

From the ‘Excel Advanced Filter‘ dialog box, we will choose ‘Copy to another location’ and then will 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

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

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

Steps:

Need to open the ‘Excel Advanced Filter’ dialog box.

Specify List Range as $A$5:$D$26

Specify Criteria Range as $A$1:$D$3

Specify ‘Copy To’ range as $F$5:$I$26

The result would be as follows:

Example #4

Now we want to find all sales of Rs. 2000-4000 and Rs. 10000-13000.

As we have four conditions as (Condition 1 AND Condition 2) OR (Condition 3 AND Condition 4).

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

That is why we have mentioned the conditions with “AND” in the same row and Conditions with “OR” in different rows.

Steps:

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

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

Criteria Range as $A$1:$D$3

‘Copy to’ Range as $F$5:$I$26

After clicking on ‘OK’. The result will be:

Example #5

Now 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 type of relations in conditions, that is why we will specify the conditions in the criteria range in different rows (OR) and different columns (AND).

Steps:

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

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

Criteria Range as $A$1:$D$3

‘Copy to’ Range as $F$5:$I$26

After clicking on OK, the result would be

Example #6 – Using WILDCARD Characters

Now we want to find all the sales transactions having a name ending with ‘esh’ or region’s first word ending with ‘st’ and want only to retrieve Name, Sales and Region only.

Here * denotes more than one characters and

‘?’ denotes only one character.

As we want only some columns not all, then we need to specify the column labels on Copy to Range before implementing the Excel advanced filter.

Now we will call the command.

Steps:

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

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

Criteria Range as $A$1:$D$3

‘Copy to’ Range as $F$5:$H$26

After clicking on ‘OK’. The Result would be:

Example #7

Now we want to filter top five sales (of a large amount).

The formula cell must evaluate to either TRUE or FALSE. As we want to get the largest 5 records that is why we have used LARGE function and compared the value with Sales Amount.

As we can see, the column heading for the formula cell is blank. We can either keep it blank or give the name, which does not match, with any of the headings of the column in the data range.

Now we will specify the ranges in ‘Excel Advanced Filter’ dialog box. Steps are:

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

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

Criteria Range as $A$1:$E$2

‘Copy to’ Range as $F$5:$I$26

After clicking on OK. The result would be like this:

Things to Remember While Using Advanced Filter in Excel

The range to which Excel advanced filter needs to be applied must have a unique heading as duplicate headings cause a problem when running an advanced filter.
There should be at least one blank row between List Range and Criteria Range.

You can download this Advanced Filter in Excel template here – Advanced Filter Excel Template

Advanced Filter in Excel

What is Advanced Filter in Excel?

How to Use Advanced Filter in Excel? (With Examples)

Example #1 – Advanced Filter in Excel with OR Function