Although the AutoFilter is nice, you are limited to filtering your list in place. Excel’s Advanced filter feature gives you the option of filtering your existing list in place or copying the data that matches your set criteria to another location in the existing worksheet or in another worksheet altogether.
However, with the Advanced Filter, you have to actually type in your criteria (instead of being able to select it from drop-down list). Which, once you understand how the criteria needs to look, really isn’t all that big of a deal.
Using the Advanced Filter with Single Criteria
Create your criteria fields in an area well to the right of or below your existing list
Click anywhere inside your list
Choose Data: Filter: Advanced Filter… from the Menu bar
Figure 8. Advanced Filter Dialog Box
4. Choose to Filter the list in-place or Copy to another location
5. Verify the List range
6. Enter the Criteria range
7. If you are copying the data to another location, enter the starting cell of the copied list location
8. Click OK
Clearing Filter Criteria
Click anywhere inside a filtered list
Choose Data: Filter: Show All from the Menu bar