This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

Microsoft Excel 2008

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. 

return to topCautions 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:

return to topUsing 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

  1. Select a cell within the database

  2. From the Data menu, select Filter » AutoFilter
    A pull-down list will be placed next to each column heading within the selected database.
    Cell with pull-down example

Running AutoFilter

  1. Activate AutoFilter

  2. 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.

  1. Activate AutoFilter

  2. From the appropriate pull-down list, select (Custom Filter…)
    The Custom AutoFilter dialog box appears.
    Custom AutoFilter dialog box
  3. From the Comparison Operator pull-down list, select a type of comparison

  4. From the Corresponding pull-down list, select or type a criteria value

  5. OPTIONAL: If you want multiple criteria, select either And or Or and repeat steps 3 and 4

  6. Click OK
    NOTE: When you use AutoFilter within a database, the row numbers will turn blue.

Turning Off the AutoFilter

  1. 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.

return to topUsing 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. 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

  1. Create a criteria range within your worksheet
    NOTE: For more information, refer to Establishing Criteria.

  2. Select any cell within your database

  3. From the Data menu, select Filter » Advanced Filter...
    The Advanced Filter dialog box appears.
    Advanced Filter dialog box

  4. 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.

  5. 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 DIALOGCollapse Dialog button
    2. Select the cells
    3. Click RESTORE DIALOGRestore Dialog button

  6. 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 DIALOGCollapse Dialog button
    2. Select the cells
    3. Click RESTORE DIALOGRestore Dialog button

  7. 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:

  1. From the Data menu, select Filter » Show All

Copying an Advanced Filter to a Second Location

Rather than filter the database in place, these steps will place the results of the advanced filter in a location that you define. The results must be placed on the current (active) worksheet.

  1. Create a criteria range within your worksheet
    NOTE: For more information, refer to Establishing Criteria.

  2. OPTIONAL: Create a placement location within your worksheet
    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.

  3. Select a cell within your database
    NOTE: If you are using a placement location, you should not select one of the heading cells.

  4. From the Data menu, select Filter » Advanced Filter...
    The Advanced Filter dialog box opens.

  5. To create a second set of information, select Copy to another location

  6. 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 DIALOGCollapse Dialog button
    2. Select the cells
    3. Click RESTORE DIALOGRestore Dialog button

  7. 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 DIALOGCollapse Dialog button
    2. Select the cells
    3. Click RESTORE DIALOGRestore Dialog button

  8. 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 DIALOGCollapse Dialog button
    2. Select the cells
    3. Click RESTORE DIALOGRestore Dialog button

  9. Click OK

Excellence. Our Measure. Our Motto. Our Goal.