March 19, 2014

Advanced Filter in Microsoft Excel

Last time we learnt how to use AutoFilter to extract information from our Excel data. Today we’ll learn how to get information from our MS Excel data using the Advanced Filter feature. You’ll find that the advanced filter is truly more powerful than the auto-filter.
Implementing the Advanced filter:

  • Select the headers in your Excel worksheet data
  • Copy them
  • Paste the header data either 2-3 columns or 2-3 rows away from the original data
  • Now below the header ‘Name of student’ enter ‘=”C*”. This tells MS Excel that we wish to filter all names beginning with ‘C’. The ‘*’ is the wild card character and represents all characters that come after ‘C’. If you wish to find names starting with ‘C’ and having, let’s say, four more characters as in Carol, then you could also type the filter term as ‘=”C????’. Here the wild card character ‘?’ represents a single character
  • Under the header label ‘Total Marks’ we type the filter term ‘>=170’.
  • In effect we will ask the advanced filter to apply two criteria and filter information about students whose names begins with ‘C’ and who got greater than or equal to 170 total marks
  • Next we click inside our data. This is important and necessary for advanced filter to work properly
  • Now we click on the Advanced Filter icon in the sort & Filter group under the Data tab
  • A new window called advanced filter pops up
  • Our complete data to be filterd is selected automatically and displayed in the window against the item ‘List Range:’
  • The criteria range is the range of cells that contains all our copied headers and the criteria that we applied in the cells below ‘Name of student’ and ‘Total Marks’. We need to select the range if it is not already displayed against the item ‘Criteria range:’ in the Advanced Filter window
  • In the advanced filter window under action you have two choices ‘Filter the list. in place’ or ‘Copy to another location’
  • For the present we’ll select the first option of ‘Filter the list. in place’
  • Click OK

You’ll see that Carol’s data is displayed which meets both the criteria of student’s name starting with ‘C’ and total marks >=170
Next we perform another advanced filter using the same criteria range but this time we place the filtered data 2-3 columns below the criteria range using the ‘copy to another location’ option

In case we have entered duplicate data we can also filter the duplicates and display only unique data by checking the check box ‘Unique records only’ visible above the OK button.
We can now appreciate how the Advanced Filter feature is indeed more powerful than the AutoFilter.

View the Excel training video:

Further reading:
Filter by using advanced criteria