Microsoft Office Tutorials and References
In Depth Information
Using Advanced Filtering
h You can specify more complex filtering criteria.
h You can specify computed filtering criteria.
h You can extract a copy of the rows that meet the criteria and place them in another location.
You can use advanced filtering with a worksheet database or with a table.
The examples in this section use a real estate listing worksheet database (shown in Figure 9-15),
which has 125 records and 10 fields. This database contains an assortment of data types: values,
text strings, logical, and dates. The database occupies the range A8:H133. (Rows above the table
are used for the criteria range.)
Figure 9-15: This real estate listing database is used to demonstrate advanced filtering.
This workbook, named real estate database.xlsx , is available on the companion
Setting up a criteria range
Before you can use the advanced filtering feature, you must set up a criteria range, which is a
range on a worksheet that conforms to certain requirements. The criteria range holds the
information that Excel uses to filter the table. The criteria range must conform to the following
h It must consist of at least two rows, and the first row must contain some or all field
names from the table. An exception to this is when you use computed criteria. Computed
criteria can use an empty Header row. (See the “Specifying computed criteria” section,
later in this chapter.)
h The other rows of the criteria range must consist of your filtering criteria.