WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Advanced Filter in Excel

Advanced Filter in Excel

By Babita SehdevBabita Sehdev | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

What is Advanced Filter in Excel?

Advanced filter is different from the auto filter in excel, this feature is not like a button which can be used on a single click of the mouse, to use an advanced filter first we have to define a criteria for the auto filter and then click on the Data tab and then in the advanced section for the advanced filter where we will fill our criteria for the data.

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

Let us learn the use of this by some examples.

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

Example #1

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

Advance Filter in Excel Example 1

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. To get the results, we can follow the steps to apply these filters in Excel.

  • Step 1: To apply an advanced filter, first, we need to select any of the cells in the data range.

Advance Filter in Excel Example - 1

  • Step 2: then click on Data tab-> Sort & Filter group -> Advanced command

Advance Filter in Excel Example - 1-1

  • Step 3: As we click on ‘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).

Advance Filter in Excel Example - 1-3

  • Step 4: For Criteria, we need to copy the column headings on the top row and define the criteria below the field heading. To specify the criteria, we can use the comparison operator, which are as follows:

Advanced Filter in Excel Example 2-6

Advance Filter in Excel Example - 1-2

  • Step 5: We want to get all records having the name ‘Suresh’ or ‘Taran.’ The Criteria Range would be like below:

Advance Filter in Excel Example - 1-4

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 an advanced filter.

  • Filter the list in place: This option filters the list at the original place, i.e., on the list range itself. After analyzing, we can remove the filter using the ‘Clear’ command in the ‘Sort & Filter’ group under ‘Data.’

Advance Filter in Excel Example - 1-5

  • 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 needs, but we will be using the 2nd option more often.

Now we need to

  • Open the ‘Advanced Filter’ dialog box

Advance Filter in Excel Example - 1-3

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

Advance Filter in Excel Example - 1-6

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

Advance Filter in Excel Example - 1-7

Example #2

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

Advance Filter in Excel Example - 1-8

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

Advance Filter in Excel Example - 1-1

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.

Advance Filter in Excel Example 2-1

Now the result is as follows:

Advance Filter in Excel Example 2-2

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 different columns. 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 ‘Advanced Filter’ dialog box.

Advance Filter in Excel Example 3

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

Advance Filter in Excel Example 3-1

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

Advance Filter in Excel Example 3-2

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

Advance Filter in Excel Example 3-3

The result would be as follows:

Advance Filter in Excel Example 3-4

Example #4

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

Advance Filter in Excel Example 4

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 ‘Advanced Filter’ dialog box, we will click on ‘Advanced’ in the ‘Sort & Filter’ group under ‘Data.’

Advance Filter in Excel Example - 1-1

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

Advance Filter in Excel Example 4-1

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

Advance Filter in Excel Example 4-2.

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

Advance Filter in Excel Example 4-3

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

Advance Filter in Excel Example 4-4

Example #5

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

Excel Example 5

As we have AND and OR, both types 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 ‘Advanced Filter’ dialog box, we will click on ‘Advanced’ in the ‘Sort & Filter’ group under ‘Data.’

Excel Example - 5-1

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

Excel Example 5-2

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

Excel Example 5-3

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

Excel Example 5-4

  • After clicking on OK, the result would be

Excel Example 5-5

Example #6 – Using WILDCARD Characters

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.

Excel Example 6

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 advanced filter.

Excel Example 6-1

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

Excel Example - 1-1

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

Excel Example 6-2

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

Excel Example 6-3

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

Excel Example 6-4

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

Excel Example 6-5

Example #7

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

Excel Example 7

The formula cell must evaluate TRUE or FALSE as we want to get the largest five records. That is why we have 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. We can either keep it empty 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 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.’

Excel Example - 1-1

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

Excel Example 7-1

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

Excel Example 7-2

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

Excel Example 7-2

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

Excel Example 7-3

Things to Remember

  • The range to which it 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 the List Range and Criteria Range.

Recommended Articles

This has been a guide to Advanced Filter in Excel. Here we discuss how to use Advanced Filter in Excel along with excel examples and a downloadable excel template. You may also look at these useful excel tools –

  • Add Filter in Excel
  • Filter Shortcut in Excel
  • Auto Filter In Excel
  • Sort Data in Excel
  • Compound Interest Formula in Excel
128 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Advanced Filter Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More