Microsoft Office Tutorials and References
In Depth Information
Filtering Data in a Table
In addition to item-by-item filtering, you can create a custom filter for any column to
show or hide rows in the list according to the criteria you specify. The exact set of options
depends on the data type. The menu option above the search box reads Text Filters,
Number Filters, or Date Filters, depending on the contents of the current column.
Options on the Text Filters menu all lead to the Custom AutoFilter dialog box, where you
can define one or two criteria for your filter. You can base criteria on the exact cell contents
(Equals, Does Not Equal) or on what the cell begins with or contains. This example finds all
rows where the contents of the Vendor column begin with B, C, D, or E.
For a column that contains numbers, most of the options on the Number Filters menu lead
to the same Custom AutoFilter dialog box. The menu contains three additional choices that
work only with numbers. The Above Average and Below Average options work as expected,
instantly filtering the list to show only those items that are above or below the arithmetic
mean. The other choice, Top Ten, is misleadingly named. It opens the Top 10 AutoFilter
dialog box, with Top 10 Items selected by default. However, you can select any number
between 1 and 500; you can choose to show the Top or Bottom entries that match that
value; and you can change Items to Percent. In this example, we’ve filtered the list to show
only those records with values in the bottom 20 percent.
The options on the Date Filters menu are probably the most extensive. You can quickly
define a range of dates, choose relative dates (Yesterday, This Quarter), or create a custom
filter. Figure 12-8 provides one example of the full range of options.