Excel Advanced Filter
Excel Filtering
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. |
| 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 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:
In Excel 2007 and later versions of Excel: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.... |