Advanced Filter in Excel – A Rarely Used Filter Feature

In our previous blog, we learned about the most used feature called Filter Feature in Excel. There we learned the meaning and purpose of Filter, multiple ways to add a filter in Excel, and how to apply a filter in single or multiple columns in Excel. We also looked into using text, number, and date filter in Excel and how to filter by color. In continuation of it, in this blog, we would unlock the Advanced Filter feature in Excel.

Sample Data

We would be using the same sample data that we used in our previous blog. Use the Download button to download the sample file to practice along with.

What is Excel Advanced Filter?

As the name in itself is self-explanatory, the Advanced Filter is a higher version of the AutoFilter feature in Excel. It helps to perform more complex filtering to the data set (which regular AutoFilter option could not perform).

This feature is available since Excel 2003 version.

Normal Filter v/s Advanced Filter

Below is the list of difference between the normal AutoFilter feature and the Advanced Excel Filter feature-

• When you filter the data set or excel data table, excel would not remove duplicate records. All the duplicate records will be visible. Unlike this, the advanced filter feature has an additional benefit of removing the duplicate records from the dataset. As a result, you get a unique list of data records, after filtering the dataset.
• The regular Filter option does not allow you to give cell reference to filter the data. However, by using the advanced filter option, you can filter the dataset by giving reference to the cell as a filter criterion.
• When you apply Filter/AutoFilter on the dataset, the existing dataset itself gets filtered (at the same location). However, the advanced filter option allows you to filter the dataset and place it at some other location in the workbook (without touching the existing dataset).
• As learned in the definition section, the Advanced Excel Filtering feature can do more complex filtering to the dataset.

With this, I can now say that you are having a high-level understanding of the added advantage of advanced filter over the normal filter.

Where is Advanced Filter Feature in Excel?

The advanced filter feature in Excel is available in the ribbon options. Follow the below navigation path for the same:

Data Tab > Sort & Filter Group > Advanced Option (as shown in the image below)

As soon as you click on the ‘Advanced’ option, the Advanced Filter dialog box would pop out on your screen.

‘Advanced Filter’ Dialog Box – Explained

The Advanced Filter Dialog Box has following options in it-

1. Action – There are two check boxes over here. The first one is – ‘Filter the list, in-place‘ which filters the existing data set table at the same place/location. Another checkbox is ‘Copy to another location‘. This copies the data set at another location and filter it at this new location, therefore, keeping the original data set intact.
2. List Range – Here you need to specify the data set or excel table range. Make sure to include the header row in the range selection.
3. Criteria Range – Here you need to specify the reference to the range of cells that contain the filtering criteria.
4. Copy To – When you want to copy the filter data to another location, you need to specify the destination location range over here.
5. Unique Records Only – Tick this checkbox if you wish to remove duplicate records while filtering the dataset.

Once of the disadvantage of using the Advanced Filter dialog box is that the List Range and the Criteria Range only accepts cells references (and not manually entered values).

Remove Duplicate Records in Excel Table

You may be aware of the ‘Remove Duplicate’ feature of Excel under the Data tab which extracts unique records from the data set. That’s true.

Then why to use the ‘Advanced Filter’ feature to remove duplicates? The answer is that – Advanced Filter feature not only removes the duplicate values in excel table but also filters the data based on specific criteria. Also, with this feature you can get the new list at some other location without changing the original data set. Let us check this out.

I have created some duplicate records in the sample data (colored as green), as shown below:

Follow the undermentioned steps to remove the duplicate records:

• Firstly, select the data set (in our case A3:E22). Make sure that you also include the header rows in the selection. If you do not select headers, then Excel would consider the first row in the selection as a header row.

• Next, open the ‘Advanced Filter’ dialog box (Data Tab > Sort & Filter Group > Advanced).

• Here, you would notice that the excel has itself filled the ‘List Range‘ input box as the selected data table.

• In the ‘Advanced Filter’ dialog box, select the radio button ‘Copy to another location‘. In the ‘Copy To‘ section, specify the cell reference of the destination location. Let us copy this at cell G3. Finally, tick the checkbox that says – ‘Unique records Only’.

As soon as you click on the ‘OK’ button, copy the data set at the destination location ($G$3) and remove the duplicate rows from there. There is no change in the original data table.

Filter Data Based on Criteria

Just like a normal filter feature in excel, the advanced filter can allow you to carry out more complex filtering based on some criteria.

An added advantage of this feature is that you can filter out the data table and copy the filtered data at some other location.

Let us learn advanced filtering starting from basic filtering and moving towards doing some complex one.

Basic Filtering Using Advanced Filter Option

Suppose, you wish to filter out all the records for ‘Female’ candidates or applications. Follow the under mentioned steps:

• Firstly, enter the criteria below the relevant heading at the copied header data. As we want to filter by ‘Female’ applications, enter the word ‘Female’ in the cell J2.

• Click anywhere in the original data set and then open the ‘Advanced Filter’ dialog box using the path – Data Tab > Sort & Filter Group > Advanced.

• In this dialog box, do the following-
  • Select the radio button ‘Copy to another location‘ under the Action section.
  • In the ‘List Range‘ input box, the original data set range would automatically appear. If it does not appear, then enter the original dataset cell range ($A$1:$E$18).
  • Enter or select the criteria cell range (including the criteria headers and value) i.e $G$1:$K$2 in the ‘Criteria Range‘ section
  • Int eh ‘Copy To‘ area, Enter the cell reference of the destination cell where you want to paste the filtered data (let us do it at cell G5).

As soon as you click on the OK button, you would notice that excel filters out the female candidate list and puts it at the destination cell G5. Also, the original data set table remains intact without any change.

In a similar way, let us now perform the same with two criteria. Let us filter and copy the data set for ‘Male’ candidates who have enrolled for ‘Masters’ degree.

To do this, simply write ‘Masters’ in cell I2 and ‘Male’ in cell J2, and follow the procedure performed above. As a result, the excel would filter for all the records starting with Masters AND for Male candidates.

Complex Filtering (Using AND and OR Operators)

In this section, we would go a bit deeper and learn to use both the ‘AND‘ as well as ‘OR‘ operator while performing advanced filter.

Suppose, we want to filter out all the records for Masters or Phd programs enrolled by only Male Candidates.

Over here the ‘Criteria Range’ would be $G$1:$K$3.

Logical Operators and Advanced Filter

You can also use the logical operators (such as greater than, less than, equal to, etc.) with the Advanced filter to filter the data set accordingly.

The logical operators work in a different manner of text data, numbers, and dates, as explained below:

Text Filter Using Logical Operators

Criteria	Explanation	Example from Sample Data
word	Show the data that begins with the specified word	Entering Bachelors will show all the data that begins with this word
=word	Show the cells that exactly matches the word	Entering =”=Bachelors of Commerce” will show the cells that exactly contain this word
<>word	Show the cells that do not exactly match the word	Entering <>Bachelors of Commerce will show all the cells that do not exactly contain this word
>word	Show the data that are alphabetically after word.	Entering >Masters in Business Administration will show data that comes after this word alphabetically
<word	Show the data that are alphabetically before word	Entering <Masters in Business Administration will show data that comes before this word alphabetically

Date and Number Filter Using Logical Operators

The Logical operators (=,>,<,<>,>=,<=) also works when you are filtering the data set based on the date (using Advanced Filter). For example, >01-04-2019 will filter the data set which falls after 01-04-2019. In a like way, <=31-12-2019 will filter the dataset which falls on or before 31-12-2019.

However, when you want to filter data based on dates that fall between two dates, you need to enter the extreme dates (start and end date) in two different columns with same headings, as shown in the image below:

Likewise, the number filter also uses similar logical operators. For example, <>10000 on the column ‘Tuition Fee’ will show all the values other than 10000.

Excel Expert Svetlana Cheusheva has many more examples for you in this link.

Advanced Filter Using Wildcard Characters in Excel

Just like normal filter, excel advanced filter feature also allows you to perform non-exact match of text in Excel and apply the filter accordingly. This is done using the wildcard character in the criteria range.

Excel supports are three types of wildcard characters which are – asterisk (*), question mark (?), and tilde (~).

• Placing Asterisk (*) before, after, and/or middle of the text means searching for any number of characters before, after, and/or middle.
• Similarly, placing Question Mark (?) before, after, and/or middle of the text means searching for a specific number of characters before, after, and/or middle. A specific number of characters here means the
• Tilde (~) before *,? or ~ means searching for the text which actually contains a wildcard characters *,? or ~ as a part of the text.

For example, *Arts* in the criteria range will search for the program which contains the word ‘Arts’ (irrespective of any number of words before or after it). Likewise, Bachelor? will search for text which has the word Bachelor followed by any one character like Bachelors. Car~* will search for the exact word Car*, and Rat~? will search for exact word Rat?.