7 Ways to Use Advance Filter

7 Ways to Use Advance Filter

Excel Advanced Filter - EBook

Self-Publishing

ALL RIGHTS RESERVED. This book contains material protected under Internationaland Federal Copyright Laws and Treaties. Any unauthorized reprint or use of thismaterial is prohibited. No part of this book may be reproduced or transmitted in anyform or by any means, electronic or mechanical, including photocopying, recording,or by any information storage and retrieval system without expressing writtenpermission from the author / publisher.

The information in this book is provided on as-it-is basis. The authors and publishershall have neither liability nor responsibility to any person or entity with respect toany loss or damages arising from the information contained in this book.

Author- Excel Forum.com and Exceltip.com

Cover Design-

Published by- Self-publishing

First Published in- April 2015

Hello User,

Greetings from ExcelForum & Exceltip!

In this E-book we are offering you 7ways of using“Advance Filter”

Advance Filter is the most powerful feature of Excel. The advanced filtering feature in Excel allows you to quickly copy unique information from one data list to another. It allows the person to quickly remove duplicates, extract records that meet certain criteria. It works great when we use wildcards, within 2 date criteria.

Filtering is a simple, however, amazing & powerful way to analyze data. Advance filter are quite easy to use. Here's how you can use Excel's advanced filtering capabilities.

Using “And”, “Or” and Multiple Criteria
How to Filter Unique Records?
How to Use Wildcards?
Extract records after specific date criteria
Extract items between 2 Date’s criteria
How to extract data with specific text?
How to extract data to another worksheet using VBA?

Let us start with creating “Advanced Filter”

Below shown picture is the example of data that we will use in this example

Click on any cell in the above mentioned data
Click on Data ribbon
From Sort & Filter group, click on Advanced

Advanced Filter dialog box will pop up
or use ALT + A + Q, ashortcut key

The List range box will automatically takes the data range (eliminates the step for user to select whole data)
In Criteria range; select the criteria range as I1:N2

Select Copy to another location & enter cell I5 in Copy to; wherein advance filtered data will be retrieved.

Click on OK button will give us the filtered data.Refer below snapshot.

All the rows which contain Manager as Susan will get extracted.

1.1And Criteria

Following picture contains range I1:N2 wherein we have couple of criteria:

1)State should be Colorado

2)Manager should be Susan

Advance filter will retrieve the data that matches both the conditions; we need to follow the below steps:

Use ALT + A + Q to launch Advanced Filter dialog box

The List range box will contain range A1:F273; the whole data table
In Criteria range,select the criteria range as I1:N2
Select Copy to another location & enter cell I5 in Copy to; wherein advance filtered data will be retrieved

Click on OK button will give us the filtered data.Refer below snapshot

In this way, we can use ‘AND’ condition to get the data that matches both the conditions.

We can have multiple conditions to get the output that matches all the conditions.

1.2OR Criteria

Following is the picture of ‘OR’ criteria

Advance filter will look for any one condition:

1)Manager should be Susan & Process should be XYZ

2)Manager should be Ronald & Process should be PMP

If any condition is met then the data will be extracted
Use ALT + A + Q to launch Advanced Filter dialog box

The full data will be listed in List range
In Criteria range, select the criteria range as I1:N3
Select Copy to another location & enter cell I5 in Copy to; wherein advance filtered data will be retrieved.

Click on OK button will give us the filtered data.Refer below snapshot

In this way, we can use ‘OR’ condition to get the data that matches any one condition & produces result accordingly.

1.3Multiple Criteria

Following is the picture of Multiple Criteria

Advance filter will check for data that matches all the entries in a row; if all the conditions will meet in a row then the data will be retrieved using advanced filter.

We need to follow the below steps:

Use ALT + A + Q to launch Advanced Filter dialog box

Enter the data range in List range field
In Criteria range, select the criteria range as I1:N3
Select Copy to another location & enter cell I5 in Copy to; wherein advance filtered data will be retrieved.

Click on OK button will give us the filtered data.Refer below snapshot

In this way, we can use multiple conditions to get the data that matches any one condition &gets us the result.

Advance filter can be used to filter unique records or removing duplicate records from the database. To get unique list of Managers, we will copy the unique list in different location other than the original list.

Note: The list must contain a heading, or the first item may be duplicated in the results.

We need to follow the below steps:

Select a cell in the database or use shortcut key ALT + A + Q
Select “Copy to another location”
In List range, select column E
Do not select anything as Criteria range
In Copy to field, enter the cell wherein you want to paste the unique records
The last step is to tick on the Unique records only

By clicking OK button, we will get the following list of all the Managers

In this way, we can use advance filter to extract unique records.

We can use Asterisk (*) & Question mark (?) wildcards.

The * Wildcard

Following is the snapshot of the example that we are going to use

From above data, we will use Wildcard Asterisk (*) to represent any number of characters in that position

We have enteredAL* in State column
Advance filter will look for all the states that start with characters as “AL”

Press ALT + A + Q for launching advance filter dialog box & follow the below ranges in the fields

This example will produce the following list of States that contain characters “AL”

The ‘?’ Wildcard

We will use question mark Wildcard (?).It represents one character within a string or text in that position

We need to follow the below steps:

We have “Boys Clothing ???Yrs”as Wildcard in Product column
Advance filter will look for all the text that meets the condition & extract the result.
The 3 question marks represent 3 characters
In this example, there are two Boys Clothing slabs i.e. 0-5 Yrs& 6-8 Yrs.
The question mark wild card will pull out both the entries

Press ALT + A + Q for launching advance filter dialog box & follow the below ranges in the fields

This example will extract the data that contains list of Products that has Boys clothing.Refer below snapshot for more information:

Following is the picture of criteria that will be referred at the time of extracting data.

In this example, any records that fall after the criteria date (>3/31/2015) will be retrieved

Press ALT + A + Q for launching advance filter dialog box & follow the below ranges in the fields

This example will extract the data that falls after the specific date.Refer below snapshot

Following is the picture of criteria that need to be looked for data that occurs between 1/1/2014 & 12/31/2014

Press ALT + A + Q for launching advance filter dialog box & follow the below ranges in the fields

This example will extract the data that contains between the two criteria dates.Refer below snapshot

We can use advance filter to derive the items that contain specific text.In this example, we will use “=Tablets” in Product column. Advance Filter will find all the rows that contain Tablets in Product column.

Following is the picture of specific text criteria

Press ALT + A + Q for launching advance filter dialog box & follow the below ranges in the fields

In this example, we will get a list of Product as Tablets.Refer below snapshot for better understanding:

We have Company Report with Employee Name, State, Emp Id, Manager & Process.

The above data contains more than 250 rows. Now, we want user to select Name, Manager& Process from dropdown lists.

The information should be updated in another sheet with a single click

To get the output on the above shown multiple criteria, we require VBA code.We need to follow the below mentioned steps to launch VB editor

Click on Developer tab
From Code group, select Visual Basic or simply press ALT +F11 to launch VBA screen

Click on Insert, and then on Module

This will create a new module.

Enter the following code in the Module

We can assign the macro to GO button. After clicking the GO button, we will get the following result

The above VBA code will automatically update the result as we change Employee Name, Manager or Process.

In this way, using Advance Filterthrough VBA,we can extract data on multiple conditions.