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 2007

Filtering Your Table

Excel 2007 lets you filter Table data according to specific criteria. Any data not matching the specified criteria is hidden from view. Filtered data, however, can be easily viewed again by removing the filter. Filtering is especially useful in large tables when you need to work only with records meeting your precise criteria. This document shows you how to filter Tables in Excel 2007.

return to topCautions for Working with Filters

When Table filtering is enabled, some Excel commands will produce different results. These can include:

NOTE: You know filtering is enabled whenever you see the filter buttons at the top of each Table column. For a graphic depicting a Table with filtering enabled, refer to Tables Overview: Table Terms.

return to topUsing Table Filters

The buttons for Table filters are added to each column of your Table. When accessed, they display column-specific pull-down menus from which you can set up a filter. For most Table filtering, this might be all you need. However, when you want to perform more complex filtering, or create a copy of your filtered information, you should use Advanced Filter.

Activating Table Filters

  1. Select a cell within the Table

  2. From the Home command tab, in the Editing group, click SORT & FILTERSort & Filter button» select Filter
    OR
    From the Data command tab, in the Sort & Filter group, click FILTERFilter button
    AutoFilter buttons appear at the top of each column of the selected Table.

Running Table Filters

  1. Activate Table Filtering

  2. In the column you want to filter, click theAutoFilter button
    The Table filter pull-down list appears, including a submenu of column-specific records you can use to filter your table.
    NOTE: By default, all records are selected (i.e., set to display).

  3. To filter the selected column, deselect the records you do not want displayed (i.e., be sure that only the records you want displayed are selected)

  4. Click OK
    All rows fitting the criteria of the selected column are displayed.
    NOTES:
    When you use AutoFilter within a Table, the row numbers of the displayed records turn blue, and the filter results appear in the status bar (e.g., 1 of 12 records found).
    The button at the top of the column changes toFilter button in filtered column

  5. To remove the filter from your Table, in the filtered column, click theFilter button in filtered column» select Clear Filter From...

Using Custom AutoFilter

Custom AutoFilter allows you to filter a range of information and/or set multiple criteria.

  1. Activate Table Filtering

  2. In the column you want to filter, click theAutoFilter button» select Text Filters or Number Filters » Custom Filter…
    The Custom AutoFilter dialog box appears.
    NOTES:
    If a column contains text, the Table filter pull-down list provides Text Filters; if the column contains numbers, Number Filters are provided.
    In the dialog box below, the column being filtered is called Amount and contains values ranging from 134.78 to 987.32, which are displayed in ascending order in the Custom AutoFilter pull-down list.
    Custom AutoFilter dialog box

  3. In the Comparison Operator pull-down list, select a type of comparison
    EXAMPLE: Select is greater than

  4. In the Corresponding pull-down list, select or type a criteria value
    EXAMPLE: Type 300

  5. OPTIONAL: If you want multiple criteria, select either And or Or and repeat steps 3 and 4
    EXAMPLE:
    In the Comparison Operator pull-down list, select is less than
    In the Corresponding pull-down list, type 500

  6. Click OK
    Your Table is filtered to display rows in the selected column containing values between 300 and 500

  7. To remove the filter from your Table, in the filtered column, click theFilter button in filtered column» select Clear Filter From...

Turning Off the AutoFilter

  1. Select a cell within the Table

  2. From the Home command tab, in the Editing group, click SORT & FILTERSort & Filter button» deselect Filter
    OR
    From the Data command tab, in the Sort & Filter group, click FILTERFilter button
    AutoFilter is disabled; the AutoFilter buttons are removed from the Table.

return to topUsing Advanced Filter

Excel's Advanced Filter has advantages not offered by the standard filter, such as its complex "and/or" filtering options. It also lets you move filtered Table data to a different area of the current worksheet.

Before You Start

Creating a criteria range
A criteria range consists of at least two rows. The first row must contain a column label, the other must provide a filtering condition.

For example, if your Table has a column labeled Assignment, the top row of the criteria could be Assignment (i.e., the column label), and the next row could be the name of a particular assignment (i.e., the condition) you want filtered.

Additional filtering conditions can be established in subsequent rows, allowing you to establish a complex filter. At least one blank row must separate your Table from your criteria range. For more information on criteria, refer to Establishing Criteria.

Running an Advanced Filter

  1. Create a criteria range within your worksheet

  2. Select any cell within your Table

  3. From the Data command tab, Sort & Filter group, click ADVANCED FILTERAdvanced Filter button
    The Advanced Filter dialog box appears.
    Advanced Filter dialog box

  4. If you want the filter to replace the current Table, select Filter the list, in-place
    NOTE: If you do not want the filter to replace the current Table, refer to Copying an Advanced Filter to Another Location.

  5. In the List range text box, type the cell range containing your Table
    OR
    To minimize the Advanced Filter dialog box so you can manually select your Table range
    1. Click COLLAPSE DIALOGCollapse Dialog button
    2. Select your Table range
    3. Click RESTORE DIALOGRestore Dialog button

  6. In the Criteria range field, type the cell range (or range name) containing the criteria
    OR
    To minimize the Advanced Filter dialog box so you can manually select cell range
    1. Click COLLAPSE DIALOGCollapse Dialog button
    2. Select the criteria range
    3. Click RESTORE DIALOGRestore Dialog button

  7. Click OK
    Your Table is filtered.
    Table row numbers turn blue.

Turning Off Advanced Filter

To remove an Advanced Filter from your Table:

  1. From the Data command tab, in the Sort & Filter group, click CLEARClear Filter button
    All Table filters are removed.

Copying an Advanced Filter to Another Location

  1. Create a criteria range within your worksheet

  2. Select a cell within your Table

  3. From the Data command tab, click ADVANCED FILTERAdvanced Filter button
    The Advanced Filter dialog box opens.

  4. Select Copy to another location

  5. In the List range text box, type the cell range containing your Table
    OR
    To minimize the Advanced Filter dialog box so you can manually select your Table range
    1. Click COLLAPSE DIALOGCollapse Dialog button
    2. Select your Table range
    3. Click RESTORE DIALOGRestore Dialog button

  6. In the Criteria range field, type the cell range (or range name) containing the criteria
    OR
    To minimize the Advanced Filter dialog box so you can manually select the cell range
    1. Click COLLAPSE DIALOGCollapse Dialog button
    2. Select the criteria range
    3. Click RESTORE DIALOGRestore Dialog button

  7. In the Copy to text box, type a cell or range in the active worksheet where the filter results will appear
    OR
    To minimize the Advanced Filter dialog box so you can manually select a cell or range
    1. Click COLLAPSE DIALOGCollapse Dialog button
    2. Select the cell or range
    3. Click RESTORE DIALOGRestore Dialog button

  8. Click OK

Excellence. Our Measure. Our Motto. Our Goal.