What is Advanced Filter Function in Excel?

Advanced Filter function of Excel is used to filter the data, when criteria to filter the data is complex. By creating a criterion through Advanced Filter function, you can extract unique data from large data base. This function is different from regular filter function of Excel which is accessible in just one click. Contrary to regular filter function of Excel, Advanced Filter function can extract the data to new location as well.

Where to find Advanced Filter Function in Excel?

Go to:
Data>Sort & Filter>Advanced 

You may use Keyboard shortcut ALT+a+q to open this function.;

How to use Advanced Filter Function?

Example 1

Let’s understand how to use Advanced Filter Function. Suppose we have sales data of Tee Limited for the month of January, 2020. You may notice duplication of products SKU-AA, SKU-AL & SKU BC in data table given below while entering the data. Tee Limited wants to remove the duplications without changing the original data base. Through Advanced Filter Function we can extract unique data i.e. any duplication will be removed and can also place that unique data on some new location whilst keeping our original data unchanged. Let’s understand how Advanced Filter Function helps in achieving this objective.

Step 1 Click the Advanced button as shown above and following dialog box will open:

Action: This refers to the location where you can save the filtered data.

List range: This refers to the data base from where you want to filter unique data.

Criteria range: This refers to our constructed criteria for extraction/filtration of data.

Copy to: This refers to cell address where you can get the filtered data.

Check unique records only.

Please note that there should be no blank rows in your data base and also select header while selecting the List range as Advanced Filter Function consider the first cell as header.

Step 2 Fill in the above dialog box:

Check that how Advanced Filter Function has filter out unique data by removing the duplication.

Example 2

Using AND criteria

Let’s move one step ahead and create a criterion for filtering the complex data base. Suppose Tee Limited sells its products in different cities of USA and wants to know its sales in New York city and greater than $2,000.

Let’s understand how Advanced Filter Function helps Tee Limited in achieving this objective.

Step 1 Copy the header and paste on another place in the work sheet.

Step 2 As we want to see sales greater than $ 2,000 in New York city therefore enter in new cell New York & >2000 below the City and Amount respectively.

Step 3 Now fill in the fields of Advanced filter function as explained above. Give criteria range by selecting the entire cell range as shown above. The window will look like.

See how Advanced Filter Function filter out the sales greater than $ 2,000 in New York city.

Example 3

Using OR criteria

Let’s take same example to understand the OR criteria. Now Tee limited wants to see its sales either in Washington or Phoenix. Repeat the steps which you have performed in example 2 above except now criteria will be specified in the same column.

The end result will be as follows:

See how Advanced Filter Function of Excel makes the filtration of large data base on given criteria and identification of any duplication/error while entering the data in just few minutes thus saving lot of time.