|Table of contents|
Excel allow you to extract data from a table based on a filter you create. In this example rows containing a specified Manufacturer are extracted and placed in another part of the spreadsheet. You need to know how to name ranges to do this.
Objective Creating an advanced filter
By the end of this page you will be able to:
Creating an Advanced Filter
Example :Click here to download vmvXL07_Eg01.xls.
A simple named cell
This short video explains how to create an advanced filter.
| Sniplet:Excel2007:Advanced Filter|
What we are going to do is to find records in a list (we will call our database), based on a filter, and place them onto the same sheet below the filter.
- Create the spreadsheet as shown (it is the same one used for the Named reference)
- Highlight the database (A6:H11) note that this includes the table headings. Name this database
- Create the filter by
- Copy the database headings (row 6 (A6:H6)) to row 13 - use [Control C to copy > Control V to paste.
- In the row below the filter heading type in the criteria, here enter Samsung to find all the Samsung drives (see notes below for other options)
- Name the filter by
- Highlight the filter heading and criteria (A13:H14) and name as filter
- Display results starting in Row 16
- Click in A16
- Select the Data tab, then in the Sort&Filter group choose Advanced
- With the list range box highlighted, click F3 to get the named ranges and choose database, then [Ok]
- Click on the Criteria Range box, then [F3] and choose criteria, then [Ok]
- In the Action area on the radio button to Copy to another location
- Click in the Copy to: box, then click on the button at the right, a small dialog box appears.
- Click on A16 and this should appear in the box.
- Click on the button on the right of the text box and you should return back to the Advanced filter dialog box
- Click [Ok] to apply the filter
.. and thats all!!
Example criteria for Manufacturer (Note: you need the = sign before the string in quotes)
- Samsung : Displays all those records with "Samsung"
- =">Samsung" : Displays all records that alphabetically come after "Samsung"
- =">S*" : Displays all records that alphabetically come after "S"
- ="<>Samsung" : Displays all records that are not "Samsung"