This topic is coverd in detail in the Microsoft Excel 2007 /2010  Advanced training course:

The Advanced filter allows the filtering of a database based on complex criteria. For example, in the database below, a filter is needed to show UK reps with salary of over £25,000 or USA reps with 2 or more years of service.

 
1. Create a Criteria range by copying the column headers to a second location (row 2 in the above example).

2. Under each heading, enter the criteria to be applied to the database. This can comprise of multiple criteria, each criteria entered on a separate row under the column headings.

3. Select any cell in the database region.

4. From the Data tab in the Sort & Filter group, select Advanced.
a. Select to filter the list in place or copy to another location.
b. List Range -Excel should detect the range of the database.

c. Criteria Range – select the criteria range created in steps 1 & 2 above including the column headings

d. Copy to:  if copying the filter result to a different location, select the location.

Note:
To copy filter results to another location, select a cell on the destination  worksheet before starting the advanced filter process.