How To Use An Advanced Filter In Excel

Advanced Filter in Excel (Table of Contents)

Advanced Filter in Excel
How to Employ Avant-garde Filter in Excel?

Advanced Filter in Excel

Advanced Filter in excel is the next level filter option available in the Data menu tab under Sort & filter section, which is used to filter the selected data equally per the criteria we ready for this. For this, first, we need to scrub the information past removing the bare cell, keeping the header to all the columns. To accelerate filter, nosotros need to define criteria by which nosotros need to filter the data, and the criteria should be placed in separated cells from the table. One time we select the Advanced Filter Option, select the complete range we want to filter, and then select the cell where we divers the criteria.

What is the difference between regular and advanced filter?

Yous can use the advanced filter for more than complex criteria filtering. (I will explain in detail with an example.)
A regular filter will filter data on the existing dataset, while with the latter, y'all can excerpt data to some other location keeping original data intact.
The advanced filter tin can be used to extract unique entries in our dataset.
Regular Filter is a sequential filter viz. yous can't use multiple criteria simultaneously to extract records from one dataset; you have to excerpt data satisfying each criterion, ultimately leading to duplicity.

Eastward.m. If y'all want to extract records where Reporting Manager is "Aakash Harit" or Employee name is "Vishal Kumar", you have to extract information 2 times using a regular filter, first information having Aakash Harit is RM, and 2d, when Emp proper name is Vishal while using an avant-garde filter, you tin extract all unique records in one become.

How to Employ Advanced Filter in Excel?

This Advanced Filter is very simple easy to employ. Permit us now see how to use the Advanced Filter in Excel with the assistance of some examples.

Yous can download this Avant-garde Filter Excel Template here – Avant-garde Filter Excel Template

Avant-garde Filter in Excel Instance #ane

Suppose you have the following dataset, and yous have to go all the records where the order total is greater than 900, and the employee name is "Nishu Kumari".

To utilize an advanced filter with some criteria, get-go, you have to copy all the headers & paste them somewhere else in the worksheet. (In my case, I am going to use blank rows to brand it easier to understand.

Now, specify the conditions under these headers according to your requirement, which will act as an input in a filter.
Now, select the entire data set along with the headers and the Go-To Data tab – Sort & Filter. This will open the Advanced Filter dialog box.

Hotkey to apply the advanced filter after selecting the dataset is (Alt central+ A+ Q).

Now, as shown above, you take to enter the post-obit details:

Activeness: Information technology has 2 options. Showtime, filter the list in identify (this will remove the original data, and the outcome of this filter volition be placed on the same location) and Second, to copy another location. (this will permit y'all to save filtered data on location)
List Range: Information technology refers to the dataset from where yous want to find information. (Here it'southward A7: K35)
Criteria Range: In this Criteria is mentioned, (A1: K2)
Copy To: This cell volition be activated if yous go with the second option in the Action criteria.
Copy Unique Records Only: Check this only if you require unique records only.

I accept entered all the details in this dialogue box and chose to copy them to some other location(A38) with unique records.
It will look somewhat as in the Screenshot on the next folio. At present click ok, and it will give all unique records.

The output is given below, where it shows only those data which contain the Employee proper name every bit Nishu Kumari.

Filtered data with complex criteria every bit constructed in a higher place.

Advanced Filter in Excel Example #2

At present, many combinations of criteria can be used in Avant-garde Filter. (Using AND and OR Criteria) Some examples are:

Filter data where Customer Name is "Chloe Jones" AND Product name is "Apple".

I have entered all the details in this dialogue box and choose to copy them to another location(N3) with unique records.

The output is given below:

Filter data where the Reporting Managing director is "Aakash Harit" AND Product Qty is greater than l.

I have entered all the details in this dialogue box and choose to copy them to another location(N9) with unique records.

The output is given beneath:

Filter data where Employee name is "Rajkumar Singh" AND Product Proper noun is "Pineapple", AND order full is greater than 100.

I have entered all the details in this dialogue box and cull to re-create them to another location(N17) with unique records.

The output is given below:

The to a higher place table shows empty data because information technology does non matches the given condition.

Filter information where Employee Proper name is "Vishal Kumar" OR Gild Full is less than 500.

I have entered all the details in this dialogue box and choose to copy them to another location(N22) with unique records.

The output is given beneath:

Filter data where Employee Name is "Abhay Gaurav" OR " Nishu Kumari".

I accept entered all the details in this dialogue box and choose to re-create them to some other location(N38) with unique records.

The output is given below:

Filter data where the Reporting Manager is "Divya Sharma" OR Product Name is "Grapes".

I have entered all the details in this dialogue box and choose to copy them to another location(N50) with unique records.

The output is given below:

From the above examples, you lot might have noticed that while using AND criteria, all values must exist mentioned in the aforementioned row and in OR criteria, all values must be in dissimilar rows.

Advanced Filter in Excel Example #iii

Another of import feature in Advanced Filtering is filtering information using Wild Menu Characters. There are wildcard characters:

An asterisk (*): Information technology represents whatever number of characters. Eastward.k. To filter information with Employee proper noun starting from "R",. You volition write "R*", So any employee proper noun starting with initials R volition be filtered out.

I have entered all the details in this dialogue box and choose to copy them to some other location(N9) with unique records.

The output is given below:

Question Marker (?): It represents ane single grapheme. E.g. filter information with where employee name initials are "Nish". Filter criteria will exist similar, "Nish?" and information technology tin mean Nishu or Nisha.

I have entered all the details in this dialogue box and choose to copy them to another location(N9) with unique records.

The output is given beneath:

Tide (~): It is used to find whatsoever wildcard character inside a text.

Avant-garde Filter in Excel Example #4

Y'all tin also remove duplicates or apace extract the unique records from the data set.

Now suppose you have the post-obit data set up, which has some duplicate entries(highlighted), and you desire to extract unique records from a dataset; you can use the advanced filter pick to do it, go along in listen that at that place is no need to mention any values in criteria option and simply cheque unique records option. This will give you records with unique values.

After applying the filter, it will give a consequence, as shown beneath:

You tin can notice in the below image; all duplicates are removed. The last 3 rows are empty

However, latest versions of Excel (mainly after Excel 2007), nosotros take an choice to remove duplicates from a dataset (Go to Data Tab.Remove Duplicates or use hotkey Alt Central+ A+ G), but it will change the original dataset. But if you desire to go on original information intact, yous can employ an advanced filter to get a dataset with unique entries at another location.

Key Notes:

While applying an advanced filter, always take care that headers in the criteria should be exactly the same every bit in the data set up. Viz. there should exist no spelling errors, space errors or even case errors, although alignment tin can be mixed. E.grand. in the above, y'all can mention criteria similar Production proper name start and employee proper noun 2d. But headers should exist exactly the aforementioned, as shown below.

You can't Disengage advanced filtering when copied to some other location.
If y'all are using an advanced filter to excerpt unique entries, make certain to select headers too, the otherwise first entry volition be taken equally headers in it.

Things to Remember

Advanced Filter is an avant-garde version of a regular filter used to filter data with complex criteria and multiple atmospheric condition.
It is used to extract unique entries from the dataset.
Yous must include headers while selecting the data set, and the headers in the criteria must be exactly in the same social club equally in the dataset.
You lot can't disengage it if filtered information is copied to another location.
The shortcut to employ an avant-garde filter after selecting the dataset is (Alt key+ A+ Q).

Recommended Manufactures

This has been a guide on Advanced Filter in Excel. Hither we discuss how to Advanced Filter in Excel along with excel examples and downloadable excel templates. Yous may also look at these useful functions in excel –