(Legacy) 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.

Cautions for Working with Filters

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

• Cell formatting affects only visible Table cells.
• When printing the Table, only visible cells will be printed.
• The Sort command will affect visible cells.
• When deleting data from the Table, entire rows must be deleted.

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.

Using 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 below.

Activating Table Filters

1. Select a cell within the Table.

2. From the Home command tab, in the Editing group, click Sort & Filter  » select Filter.
   OR
   From the Data command tab, in the Sort & Filter group, click Filter. 
   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 the .
   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 to 

5. To remove the filter from your Table, in the filtered column, click the  » 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 the  » 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. 
   

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 the  » 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 & Filter  » deselect Filter.
   OR
   From the Data command tab, in the Sort & Filter group, click Filter. 
   AutoFilter is disabled; the AutoFilter buttons are removed from the Table.

Using 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 Filter.  
   The Advanced Filter dialog box appears.
   

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

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 Dialog. 
   2. Select your Table range.

   3. Click Restore Dialog. 

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 Dialog. 
   2. Select the criteria range.

   3. Click Restore Dialog. 

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 Clear. 
   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 Filter. 
   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 Dialog. 
   2. Select your Table range.

   3. Click Restore Dialog. 

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 Dialog. 
   2. Select the criteria range.

   3. Click Restore Dialog. 

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 Dialog. 
   2. Select the cell or range.

   3. Click Restore Dialog. 

8. Click OK.