The Excel Advanced Filter
Related Page:
Excel Autofilter
The Excel Advanced Filter can be used to perform more complex filtering than the basic Excel Autofilter.
The Advanced filter is used to filter a data set, depending 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 a drop-down menu.
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 follows:
| list_range | - | The range of cells that you want to filter. This range should include headers at the top of each column. |
| criteria_range | - |
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 above spreadsheet on the right has been prepared to perform 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 spreadsheet translate to the condition:
( Maths % >= 60 AND Science % >= 60 AND English % >=60 ) OR Maths % >=80
To apply the advanced filter to the example spreadsheet:
In Current Versions of Excel (Excel 2007 and later:Select the Data tab at the top of your spreadsheet, and select Advanced from the Filter options. 
|
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.... 
|
This causes the Excel Advanced Filter dialog box to pop up (see rightabove).
For the above example spreadsheet, the data is in a worksheet named 'Sheet1'. Therefore, the List range: field in the dialog box should be entered as Sheet1!A5:D17, and the Criteria range should be entered as Sheet1!B1:D3.
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, applied to the example spreadsheet, is shown
on the rightabove.
As expected, the rows that have been displayed are those satisfying the criteria:
( Maths % >= 60 AND Science % >= 60 AND English % >=60 ) OR Maths % >=80
Filtering Text Values
The rules for applying the advanced filter to text values are listed in the following table:
| ="=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 occurrence 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:
- the * symbolises any number of characters
- the ? symbolises any single character.
Also note 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".
A useful video showing use of the Excel Advanced Filter is available on the Microsoft Office website.