Subject Line

Use Advanced Filter for simplification of complex search

Overview

Sometimes you have filter data based on multiple and complex criteria. With the help of advanced filter you can filter data based on complex criteria. This works as a SEO or search engine which searches on multiple criteria.

Working

Let’s understand the working of the advanced filter with the help of an example.

We have Employee data as below from row number 7

Employee data

Let’s setup the options on which we want to search

1. Let’s find out employees with Designation as Sr. Consultant

Step 1: Enter Sr. Consultant in Designation column in cell C2

Step 2: Go to DATA tab

Step 3: Click on Advanced option in Sort & Filter group

Step 4: Select the List range i.e. Cell A7 to H27

Step 5: Select the Criteria range i.e. Cell A1 to H2 and click on OK

Step 6: Employees with Designation Sr. Consultant appears as a result

Step 7: Go to DATA tab

Step 8: Click on Clear option in Sort & Filter group

1. Let’s find out Employees with Designation Sr. Consultant and who are from Central region

Step 1: Enter Sr. Consultant in Cell C2 and Central in Cell F2

Step 2: Go to DATA tab

Step 3: Click on Advanced option in Sort & Filter group

Step 4: Select the List range i.e. Cell A7 to H27

Step 5: Select the Criteria range i.e. Cell A1 to H2 and click on OK

Step 6: Employees with Designation Sr. Consultant and who are from Central region appears as a result

Step 7: Go to DATA tab

Step 8: Click on Clear option in Sort & Filter group

1. Let’s find out Employees with Designation Sr. Consultant and who are from Central region or North region

Step 1: Enter Sr. Consultant in Cell C2 and Central in Cell F2

Step 2: Enter Sr. Consultant in Cell C2 and North in Cell F3

Step 3: Go to DATA tab

Step 4: Click on Advanced option in Sort & Filter group

Step 5: Select the List range i.e. Cell A7 to H27

Step 6: Select the Criteria range i.e. Cell A1 to H3 and click on OK

Step 7: Employees with Designation Sr. Consultant and who are from Central & North region appears as a result

Step 8: Go to DATA tab

Step 9: Click on Clear option in Sort & Filter group

1. Let’s take salary as filter in this example

Let’s find out the employee with Designation Sr. Consultant and who is from Central region and whose salary is less than 5000

Step 1: Enter Sr. Consultant in Cell C2 and Central in Cell F2

 Step 2: Enter <5000 in H2 cell

Step 3: Go to DATA tab

Step 4: Click on Advanced option in Sort & Filter group

Step 5: Select the List range i.e. Cell A7 to H27

Step 6: Select the Criteria range i.e. Cell A1 to H2 and click on OK

Step 7:  Employees with Designation Sr. Consultant and who are from Central region and whose monthly salary is less than 5000 appears as a result

Step 8: Go to DATA tab

Step 9: Click on Clear option in Sort & Filter group

1. Let’s now find out employees whose experience is greater than 5 Years

Step 1: Enter >5 in Cell D2

Step 2: Go to DATA tab

Step 3: Click on Advanced option in Sort & Filter group

Step 4: Select the List range i.e. Cell A7 to H27

Step 5: Select the Criteria range i.e. Cell A1 to H2 and click on OK

Step 6: Employees whose experience is greater than 5 years appears as a result

Step 7: Go to DATA tab

Step 8: Click on Clear option in Sort & Filter group

1. Let’s see how unique records only option works with an example

Step 1: Select the Designation Range i.e. Cell C7 to C27

Step 2: Go to DATA tab

Step 3: Click on Advanced option in Sort & Filter group

Step 4: Select the List range i.e. Cell A7 to H27

Step 5: Select the Cell K7 in Copy to box from the Advance Filter sheet

Step 6: Select the Unique records only tick box

Step 7: Click on OK

Step 8: Unique designation is copied to cell K7

Let’s see how unique records can be copied to another location option works with an example

Step 1: Go to DATA sheet since we want the Unique designation in DATA sheet.

Step 2: Go to DATA tab

Step 3: Click on Advanced option in Sort & Filter group

Step 4: Select the Copy to another location in Action radio button

Step 5: Select the List range i.e. Cell A7 to H27

Step 6: Select the Cell A1 in Copy to box from the DATA sheet

Step 7: Select the Unique records only tick box

Step 8: Click on OK

Step 9: Unique designation is copied to DATA sheet

Scope of usage

• Can be used to define the search criteria

Can be used to fetch records using complex criteria