Advanced Filtering - Using Criteria to Filter Lists

(NB You should load the file Winelist.xls into Excel for this exercise. Winelist.xls is an Excel spreadsheet containing some 50 wines and is located on your WebBooks disc in the 'start\adspread' directory.)

The Advanced Filter command enables you to find records by using more complex criteria specified in a Criterion Range.

This allows complex queries such as

"Wines produced by Hardys that cost more than $10 or are Dry Red and cost more than $20."

Creating a Criteria Range

To carry out the more advanced searchs a separate Criteria Range must be created elsewhere on the spreadsheet.

This consists of the Database Field Names together with at least one blank line beneath them.

As the field names will be the link between the Database and the Criterion Range it is important to Copy the field names to a different location on the worksheet.

Once the field names have been copied enter the desired criteria under the appropriate field name.

For example if we want wines costing more than $5 enter >5 under the Cost field in the Criterion range

Use the same line for AND operations and Separate lines for OR operations.

Setting the Criterion Range

The Criteria Range is set via the menu options

Ensure the active cell is somewhere in the database.

Data - Filter - Advanced - Filter

Enter the range of cells in the Criteria Range box.

For example,
The following search is for dry reds that cost more than $10.00

---