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 trul more powerful than the autofilter.
Implementing the Advanced filter:
Select the headers in your Excel worksheet data
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 insid 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
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'
You'll see that Carol's data is displayed which meets
both the criteria of student's name starting with 'C' and total marks
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'
In case we have entered duplicat 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.