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
Using AutoFilter
Using Advanced Filter

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.

Activating Table Filters

Select a cell within the Table
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

Activate Table Filtering
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).
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)
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
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.

Activate Table Filtering
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.
In the Comparison Operator pull-down list, select a type of comparison
EXAMPLE: Select is greater than
In the Corresponding pull-down list, select or type a criteria value
EXAMPLE: Type 300
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
Click OK
Your Table is filtered to display rows in the selected column containing values between 300 and 500
To remove the filter from your Table, in the filtered column, click the» select Clear Filter From...

Turning Off the AutoFilter

Select a cell within the Table
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

Create a criteria range within your worksheet
Select any cell within your Table
From the Data command tab, Sort & Filter group, click ADVANCED FILTER
The Advanced Filter dialog box appears.
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.
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
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
Click OK
Your Table is filtered.
Table row numbers turn blue.

Turning Off Advanced Filter

To remove an Advanced Filter from your Table:

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

Copying an Advanced Filter to Another Location

Create a criteria range within your worksheet
Select a cell within your Table
From the Data command tab, click ADVANCED FILTER
The Advanced Filter dialog box opens.
Select Copy to another location
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
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
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
Click OK