Microsoft Office Tutorials and References
In Depth Information
Creating Custom Filters
The Custom AutoFilter dialog box provides
yet another more specific filter
opportunity. Suppose you want to search for a
word that begins with M, but is only
eight characters long. Use the question
mark symbol as a wildcard. For example,
if you enter M???????, Excel will return
Missouri, Maryland, and Michigan. The ?
takes the place of a single character. Use
an asterisk (*) as a wildcard if you don’t
know the exact number of characters.
The Custom AutoFilter dialog box.
Using PivotTables on Columns
All of Excel’s ﬁlters temporarily hide rows
that contain the data you want to ﬁlter
out of the worksheet. The ﬁlters cannot be
applied to columns. If you need to ﬁlter data
from a column, you can create a PivotTable
to reposition your data. Learn how to do
this in Chapter 15 “Using PivotTables.”
You can use the Custom AutoFilter dialog box
(see Figure 8-21) to compare more than one
criterion. Excel uses the comparison operators
AND and OR to combine comparison filters. For
instance, creating a custom filter that begins
with M and ends with I would find both
Mississippi and Missouri. A custom filter that
begins with F or begins with G would find both
Florida and Georgia.
The AutoFilter feature hides rows that don’t
meet your criteria, including rows holding
your summary data. Be sure to check the
summary row in your ﬁlter criteria if you
want to display a calculation of the entire
database, not just your ﬁltered data, as
shown in Figure 8-22.
To create a Custom AutoFilter, select your initial
filter using one of the methods described in this
chapter. Then, in the Custom AutoFilter dialog
box, choose the AND or OR option. From the
second drop-down menu, select another filter
option and type your filter term in the box. Click
OK to apply the filter.