The Excel Advanced Filter can be used to perform more complex filtering than the basic Excel Autofilter.
Instead of filtering by fixed values or simple criteria, the advanced filter depends on user-defined criteria, that can be applied to several columns of data simultaneously. These criteria are specified on the same spreadsheet as the range to be filtered, rather than in the filter command box.
In order to perform an Excel advanced filter, you need to specify a list_range and a criteria_range. These ranges both specify ranges of cells on your working spreadsheet. They are defined as:
|list_range||-||The range of cells that you want to filter. This range should include headers at the top of each column.|
A range of cells (generally positioned above or below the list_range), in which the filtering criteria are specified.
The criteria_range should be headed by headers that match the list_range headings. The criteria for the corresponding rows in the list_range should be listed under each of these headings.
The Excel Advanced Filter is best explained by way of an example.
The spreadsheet on the right shows the set-up for an Excel advanced filter on the range of cells A5 - D17 (the list_range)
The Criteria to be used for the filter are listed in the cells B1 - D3 (the criteria_range)
The first row of the criteria_range is the header row and the actual criteria are listed below this.
Criteria listed on the same row are linked by the "AND" operator, and criteria listed on different rows are linked by the "OR" operator. Therefore, the criteria in the example translate to the condition:
( Maths % >= 60 AND Science % >= 60 AND English % >=60 ) OR Maths % >=80
|In order to apply the advanced filter to the example spreadsheet:|
In Excel 2003:
Select the Data menu at the top of your spreadsheet, and from this, select the option Filter, and then the option Advanced Filter....
In Excel 2007 or Excel 2010:
Select the Data tab at the top of your spreadsheet, and select Advanced from the Filter options.
This causes the Excel Advanced Filter options box to pop up. This is the same in Excel 2003 and Excel 2007 and is shown on the right.
As shown, in this example, the list_range should be input as A5:D17, and the criteria_range should be input as B1:D3 on the current worksheet.
If you want to display the list in place, simply click OK. However, if you wish to copy the result of the filter into a new location, this can also be specified at this stage.
The result of the Excel advanced filter is shown in the spreadsheet on the right.
As expected the rows that have been displayed are those satisfying the criteria :
The criteria definitions for filtering numbers for the Excel advanced filter are straightforward, in that the standard mathematical operators (eg. "=", "<", "<>") are used. However, the rules for applying the advanced filter to text values are less obvious. Therefore, the following table has been provided as a guide.
|="=text"||Select cells whose contents are exactly equal to the string "text"|
|<>text||Select cells whose contents are not equal to the string "text"|
|text||Select cells whose contents begin with the string "text"|
|>text||Select cells whose contents are ordered (alphabetically) after the string "text"|
|*text*||Select cells whose contents contain the string "text"|
|text*text||Select cells whose contents begin with the string "text" AND contain a second occurrance of the string "text"|
|="=text*text"||Select cells whose contents begin with the string "text" AND end with the string "text"|
|?text||Select cells whose contents begin with any single character, followed by the string "text"|
|="=text?text"||Select cells whose contents begin with the string "text" AND end with the string "text" AND contain exactly one character between these two strings|
|="=???"||Select cells whose contents contain exactly 3 characters|
In the above table, two wildcards have been used. These are:
It should also be noted that Excel filters are not case-sensitive so, for example, a filter based on the string "text" returns exactly the same result as a filter based on the string "TEXT".
Further information and examples of the Excel Advanced Filter can be viewed at the Microsoft Office website