Microsoft Excel 2003/2004

Filtering Your Database

Using a filter allows you to work with a portion of the records in your database by extracting only records that match a set of criteria.

Cautions for Working with Filters
Using AutoFilter
Using Advanced Filter

Cautions for Working with Filters

Commands in Excel can have different results on database records while using filters.

NOTE: The pull-down lists located at each column heading help remind you that the filtering is turned on.

Some effects of filtering a database include the following:

Cell formatting affects only visible cells in the database.
When printing the database, only visible cells will be printed.
The Sort command will affect visible cells.
When deleting data from the database, entire rows must be deleted.
Most of the Edit menu commands affect entire rows, not individual fields (cells).

Using AutoFilter

AutoFilter works for most filtering needs, but when you have complex criteria or want to create a copy of the information, use Advanced Filter. The AutoFilter command applies pull-down lists directly to the column headings in the database. These menus are used to select the field contents, which determine what records will display.

Activating AutoFilter

Select a cell within the database
From the Data menu, select Filter » AutoFilter
A pull-down list will be placed next to each column heading within the selected database.

Running AutoFilter

Activate AutoFilter
From the appropriate pull-down list, select a value to use as filter criteria
Rows meeting the filter criteria are now displayed.
NOTE: When you use AutoFilter within a database, the row numbers will turn blue, and the results of the filter will appear in the status bar (e.g., 1 of 12 records found).

Using Custom Filter

A custom filter allows you to select a "range" of information or set multiple criteria.

Activate AutoFilter
Windows: From the appropriate pull-down list, select (Custom…)
Macintosh: From the appropriate pull-down list, select (Custom Filter…)
The Custom AutoFilter dialog box appears.
From the Comparison Operator pull-down list, select a type of comparison
From the Corresponding pull-down list, select or type a criteria value
OPTIONAL: If you want multiple criteria, select either And or Or and repeat steps 3 and 4
Click OK
NOTE: When you use AutoFilter within a database, the row numbers will turn blue.

Turning Off the AutoFilter

From the Data menu, select Filter » AutoFilter
NOTE: AutoFilter is active if a checked box appears before it; it is inactive if no checked box is present.

Using Advanced Filter

The Advanced Filter command allows you to extract the records in your database based on a criterion and then move the results to a different location on the current worksheet. It also allows you to place the results onto a new worksheet so that you can overcome the problems of using certain features of Excel (printing, formatting, and editing options) while your database is filtered. Advanced Filter has advantages not offered by the AutoFilter command. For example, it allows you to filter using complex "and/or" criteria.

Before You Start

Creating a criteria range
A criteria range consists of at least two rows. The first row must contain a column label(s). The other row(s) contains your filtering condition. Additionally, at least one blank row must be between the criteria range and your database. For examples of which criteria may be used, refer to Establishing Criteria.

Establishing a Placement Location (optional)
A placement location is a row with column headings for the types of information you want to place in a different location. You do not need to include all the headings of the original database and the headings that you do use can be in a different order. For example, you may have a large database of conference attendees and want to filter only the attendees' names and whether their registration fee has been paid and send that data to another sheet in your workbook.

WARNING: If there is any information under the headings for the placement location, it will be deleted. As a precaution, you may want to place the placement headings on a separate sheet in the workbook file.

Running an Advanced Filter

Create a criteria range within your worksheet
NOTE: For more information, refer to Establishing Criteria.
Select any cell within your database
From the Data menu, select Filter » Advanced Filter...
The Advanced Filter dialog box appears.
If you want the filter to replace the current database, select Filter the list, in-place
NOTE: If you do not want the filter to replace the current database, refer to Copying an Advanced Filter to a Second Location.
In the List range text box, type the range of cells containing your database
OR
To minimize the Advanced Filter dialog box so you can select the range of cells manually,
1. Click COLLAPSE DIALOGor
2. Select the cells
3. Click RESTORE DIALOGor
In the Criteria range field, type the range of cells or the range name containing the criteria
OR
To minimize the Advanced Filter dialog box so you can select the range of cells manually,
1. Click COLLAPSE DIALOGor
2. Select the cells
3. Click RESTORE DIALOGor
Click OK
NOTE: With a filtered "in-place" database, the row numbers will turn blue.

Turning Off Advanced Filter

To remove filters applied to all columns in a range or list:

From the Data menu, select Filter » Show All

Copying an Advanced Filter to a Second Location

Create a criteria range within your workbook
NOTE: For more information, refer to Establishing Criteria.
OPTIONAL: Create a placement location within your workbook
NOTES:
For more information, refer to Establishing a Placement Location.
A placement location is not necessary if you will be using the same headings in the same order as your original database.
Select a cell within your database
NOTE: If you are using a placement location, you should not select one of the heading cells.
From the Data menu, select Filter » Advanced Filter...
The Advanced Filter dialog box opens.
To create a second set of information, select Copy to another location
In the List range text box, type the range of cells containing your database
OR
To minimize the Advanced Filter dialog box so you can select the range of cells manually,
1. Click COLLAPSE DIALOGor
2. Select the cells
3. Click RESTORE DIALOGor
In the Criteria range text box, type the range of cells or the range name containing the criteria
OR
To minimize the Advanced Filter dialog box so you can select the range of cells manually,
1. Click COLLAPSE DIALOGor
2. Select the cells
3. Click RESTORE DIALOGor
In the Copy to text box, type the range of cells to which you want to copy the filter
OR
To minimize the Advanced Filter dialog box so you can select the range of cells manually,
1. Click COLLAPSE DIALOGor
2. Select the cells
3. Click RESTORE DIALOGor
Click OK