Microsoft Office Tutorials and References
In Depth Information
Figure 9-19: The result of applying an advanced filter.
Clearing an advanced filter
When you apply an advanced filter, Excel hides all rows that don't meet the criteria you specified. To clear the
advanced filter and display all rows, choose Data ⇒ Sort & Filter ⇒ Clear.
Specifying Advanced Filter Criteria
The key to using advanced filtering is knowing how to set up the criteria range, which is the focus of the sec-
tions that follow. You have a great deal of flexibility, but some of the options are not exactly intuitive. Here,
you'll find plenty of examples to help you understand how to create a criteria range that extracts the information
The use of a separate criteria range for advanced filtering originated with the first ver-
sion of Lotus 1-2-3, nearly 30 years ago. Excel adapted this method, and it has never
been changed, despite the fact that specifying advanced filtering criteria remains one of
the most confusing aspects of Excel. Fortunately, however, Excel's standard filtering is
sufficient for most needs.