Microsoft Excel Advanced Filter

Advanced Filter

The Advanced Filter is similar to the Autofilter, the Autofilter is fully automated whereas the Advanced filter we have to set up manually with the help of an Excel wizard.

The benefit of the Advanced Filter is that you can enter more complex criteria(s), the data can be filtered in-place or copied to another sheet.

Setting up the Advanced Filter.

1.     Copy the header row of your list and paste one set of headers to the right hand side of the list, separating them by at least one column or row from the list, This will act as your criteria headings.

2.     Enter the Criteria values.

Once you have entered all the criterias that you wish to extract from the List, you are ready to start the Advanced Filter wizard.

1.   Select from the Data tab the             

Advanced Button.

Tip: Place your cursor within the List Range before you start the wizard, this will save you highlighting the range in the wizard.

Select the Copy to another location if you wish to place the filtered data either beside or on another sheet, if this option is not selected the data will be filtered in place (the original List will be hidden).

List Range: The List range box will be automatically completed.

Criteria Range: Highlight the criteria including the headings.

Copy to: Select the location where you wish to place the results (filtered data “Extract”).

This option will not be able to be selected unless you have chosen the Copy to another location.

Unique records only: If you origional list has duplicate records, tick this box so that duplicates records are not shown.

Reserved Names

You can name a range "Criteria", and the reference for the range will appear automatically in the Criteria range box. Define the name "Database" for the range of data to be filtered and define the name "Extract" for the area where you want to paste the rows, and these ranges will appear automatically in the List range and Copy to boxes. Advanced filter is advanced in years as well, this is the old, original design of the Excel data filter from the early 1990's long

before AutoFilter arrived.

Database Functions.

There is a range of database functions that can be performed with the advanced filter, below we have listed a few of the more frequently used database functions.

Firstly we must understand the syntax of the function, to show this we are using the DSUM function using the above advanced filter example.