Advanced Filter in Microsoft Excel 2003

Advanced Filter is the most powerful way of filtering data in Excel. It takes a lot more work to set up than Autofilter, but goes beyond Autofilter's limitations.
The advantages to using Advanced Filter include:

• Run more complex filters
• Copy filtered results to another area on the worksheet
• Filter for unique records

The criteria that the Advanced Filter uses for its filtering are held in a separate range outside the range of data being filtered. This needs to be set up prior to using the Advanced Filter.

Apply an Advanced Filter

1. Copy the desired column labels from the range to be filtered to the first row of the criteria range
The example below is being setup to filter for the products in the list with a stock level of less than 20 and a stock value of more than £200. For this to work I would need the Stock Level and Stock Value labels in the criteria range.
In this example column labels have also been copied into cells H7:I7. This is optional, and is being used to extract the filtered data to. In this example I would only like to see the data from the Code and Product columns in my filter results.

The criteria range can be placed in any open cells on your worksheet and you only need to copy the column labels of the columns you will filter by.

2. Enter the criteria you want to filter for below the criteria labels
• Criteria entered on the same line must occur in the same record (And logic)
• Criteria entered on separate lines must occur in different records (Or logic)
• Criteria headings with no data under them are not filtered
• Duplicate criteria headings allow more than one set of criteria to be entered i.e. for a date column you may use less than and specific date and greater than a specific date under separate criteria headings


3. Click Data > Filter and then Advanced Filter from the submenu
The Advanced Filter dialogue box appears


4. Select either the Filter the list, in-place (like Autofilter) or Copy to another location option button
5. In List range, select the range of data to be filtered
6. In Criteria range, select the criteria range
7. If copying the filter results to another location, enter the range you wish to copy to in the Copy to field


8. Click Ok