How To Advance Filter In Excel

Sentry Video – Excel Advanced Filter

Excel Advanced Filter is i of the most underrated and nether-utilized features that I have come up across.

If you work with Excel, I am sure you have used (or at to the lowest degree heard most the regular excel filter). It quickly filters a data set based on selection, specified text, number or other such criteria.

In this guide, I will testify you some cool stuff y'all tin do using the Excel advanced filter.

But Starting time… What is Excel Avant-garde Filter?

Excel Advanced Filter – as the name suggests – is the advanced version of the regular filter. You can use this when you need to use more complex criteria to filter your data set.

Here are some differences between the regular filter and Advanced filter:

While the regular data filter volition filter the existing dataset, you can use Excel advanced filter to extract the data ready to some other location equally well.
Excel Advanced Filter allows you lot to employ complex criteria. For example, if yous take sales data, you can filter data on a benchmark where the sales rep is Bob and the region is either North or South (we volition see how to do this in examples). Office support has some proficient explanation on this.
You tin use the Excel Avant-garde Filter to extract unique records from your data (more on this in a second).

EXCEL Advanced FILTER (Examples)

At present let's have a look at some example on using the Advanced Filter in Excel.

Example 1 – Extracting a Unique list

Y'all tin use Excel Advanced Filter to speedily extract unique records from a data fix (or in other words remove duplicates).

In Excel 2007 and afterwards versions, in that location is an pick to remove duplicates from a dataset. But that alters your existing data set. To keep the original data intact, y'all need to create a copy of the information and then use the Remove Duplicates choice. Excel Avant-garde filter would allow you to select a location to go a unique list.

Allow's meet how to use an avant-garde filters to become a unique list.

Suppose y'all accept a dataset as shown below:

As yous can encounter, there are duplicate records in this data set (highlighted in orangish). These could exist due to an error in information entry or result of data compilation.

In such a case, you can use Excel Avant-garde Filter tool to quickly become a list of all the unique records in a different location (so that your original data remains intact).

Hither are the steps to go all the unique records:

This will instantly give you a list of all the unique records.

Caution: When yous are using Advanced Filter to get the unique list, make sure you take as well selected the header. If you don't, it would consider the first cell every bit the header.

Example 2 – Using Criteria in Excel Advanced Filter

Getting unique records is ane of the many things you lot can exercise with Excel advanced filter.

Its primary utility lies in its ability to permit using complex criteria for filtering data.

Here is what I mean past circuitous criteria. Suppose you have a dataset as shown below and you want to quickly get all the records where the sales are greater than 5000 and the region is the US.

Here is how you can use Excel Avant-garde Filter to filter the records based on the specified criteria:

This would instantly requite you all the records where the region is the Us and the sales are more than 5000.

The above example is a instance where the filtering is done based on 2 criteria (Usa and sales greater than 5000).

Excel Advanced filter allows yous to create many different combinations of criteria.

Hither are some examples of how you lot tin construct these filters.

Using the AND Criteria

When you want to apply AND criteria, y'all need to specify information technology below the header.

For instance:

Using the OR Criteria

When yous want to use OR criteria, you demand to specify the criteria in the same column.

For example:

By now, you must accept realized that when we have the criteria in the same row, it is an AND criteria, and when we have information technology in unlike rows, it is an OR criteria.

Example 3 – Using WILDCARD Characters in Advanced Filter in Excel

Excel Advanced Filter likewise allows the usage of wildcard characters while constructing the criteria.

There are three wildcard characters in Excel:

* (asterisk) – Information technology represents any number of characters. For example, ex* could mean excel, excels, instance, good, etc.
? (question marker) – Information technology represents one single character. For instance, Tr?mp could mean Trump or Tramp.
~ (tilde) – It is used to place a wildcard character (~, *, ?) in the text.

Now let's run across how we can apply these wildcard characters to practise some advanced filtering in Excel.

To filter records where the sales rep name starts from J.

Note that * represent any number of characters. And then any rep with the name starting with J would be filtered with these criteria.

Similarly, yous can utilize the other two wildcard characters equally well.

Note: In case you're using Office 365, you should check out the FILTER office. It can do a lot of things that advanced filter tin practise with a simple formula.

Note:

Remember, the headers in the criteria should be exactly the same as that in the data set up.
Avant-garde filtering cannot be undone when copied to other locations.

Y'all May Likewise Like the Following Excel Tutorials:

Dynamic Excel Filter – Extract Data as yous Type.
Filtering Cells with Bold Font Formatting.
How to Filter Cells that have Duplicate Text Strings (Words) in information technology.
How to Filter Information in a Pin Table in Excel
MS Guide for Avant-garde Filter in Excel.
How to Compare Ii Columns in Excel.
Excel VBA Autofilter.