Microsoft Office Tutorials and References
In Depth Information
You can put the criteria range anywhere in the worksheet or even in a different worksheet. However, you should
avoid putting the criteria range in rows that are occupied by the list or table. Because Excel may hide some of
these rows when filtering, you may find that your criteria range is no longer visible after filtering. Therefore,
you should generally place the criteria range above or below the table.
Figure 9-17 shows a criteria range in A1:B2, located above the list that it uses. Notice that the criteria range
does not include all the field names from the table. You can include only the field names for fields that you use
in the selection criteria.
Figure 9-17: A criteria range for advanced filtering.
In this example, the criteria range has only one row of criteria. The fields in each row of the criteria range are
joined with an AND operator. Therefore, after applying the advanced filter, the list shows only the rows in
which the Bedrooms column is 3 and the Pool column is TRUE. In other words, it shows only the listings for
three-bedroom homes with a pool.
You may find specifying criteria in the criteria range a bit tricky. I discuss this topic in detail later in this chapter
in the section, “Specifying Advanced Filter Criteria.”
Applying an advanced filter
To perform the advanced filtering:
1. Ensure that you've set up a criteria range.
2. Choose Data ⇒ Sort & Filter ⇒ Advanced.
Excel displays the Advanced Filter dialog box, as shown in Figure 9-18.