Microsoft Office Tutorials and References
In Depth Information
Filtering Data Lists
If you find that filtering the data list by selecting a single value in a field
dropdown list box gives you more records than you really want to contend with,
you can further filter the database by selecting another value in a second
field’s drop-down list. For example, suppose that you select Boston as the
filter value in the Location field’s drop-down list and end up with hundreds of
Boston records displayed in the worksheet. To reduce the number of Boston
records to a more manageable number, you could then select a value (such
as Human Resources) in the Dept field’s drop-down list to further filter the
database and reduce the records you have to work with onscreen. When you
finish working with the Boston Human Resources employee records, you can
display another set by displaying the Dept field’s drop-down list again and
changing the filter value from Human Resources to some other department,
such as Accounting.
When you’re ready to display all the records in the database again, click the
filtered field’s AutoFilter button (indicated by the appearance of a cone filter
on its drop-down button) and then click the Clear Filter from (followed by the
name of the field in parentheses) option near the middle of its drop-down list.
You can temporarily remove the AutoFilter buttons from the cells in the top
row of the data list containing the field names and later redisplay them by
clicking the Filter button on the Data tab or by pressing Alt+AT or Ctrl+Shift+L.
Using ready-made number filters
Excel contains a number filter option called Top 10. You can use this option
on a number field to show only a certain number of records (like the ones
with the ten highest or lowest values in that field or those in the ten highest
or lowest percent in that field). To use the Top 10 option to filter a database,
follow these steps:
1. Click the AutoFilter button on the numeric field you want to filter with
the Top 10 option. Then highlight Number Filters in the drop-down list
and click Top 10 on its submenu.
Excel opens the Top 10 AutoFilter dialog box. By default, the Top 10
AutoFilter chooses to show the top ten items in the selected field. However,
you can change these default settings before filtering the database.
2. To show only the bottom ten records, change Top to Bottom in the
left-most drop-down list box.
3. To show more or fewer than the top or bottom ten records, enter the
new value in the middle text box (that currently holds 10) or select a
new value by using the spinner buttons.