Microsoft Office Tutorials and References
In Depth Information
Using Advanced Filtering
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 worksheet
database 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-16 shows a criteria range in A1:B2, above the worksheet database 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-16: 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 (except for the Header row) are joined with an AND operator. Therefore, after applying
the advanced filter, the worksheet database shows only the rows in which the Bedrooms field is 3
and the Pool field 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
Excel displays the Advanced Filter dialog box, as shown in Figure 9-17.
3. Excel guesses your database range if the active cell is within or adjacent to a block of
data, but you can change it if necessary.
4. Specify the criteria range.
If you happen to have a named range with the name Criteria, Excel will insert that range
in the Criteria Range field — you can also change this range if you like.