How To Use Filter On Excel 2007

COVID-19 Notice

Learn more virtually UWEC's COVID-nineteen response.

(Archives) Microsoft Excel 2007: Filtering Your Table

Last updated Monday, Aug. 31, 2020, at 10:27 a.m.

This commodity is based on legacy software.

Excel 2007 lets you lot filter Table data according to specific criteria. Whatsoever information not matching the specified criteria is hidden from view. Filtered data, however, can be hands viewed again by removing the filter. Filtering is especially useful in large tables when y'all need to work just with records coming together 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, but visible cells will be printed.
The Sort command will affect visible cells.
When deleting data from the Table, unabridged rows must be deleted.

NOTE: You know filtering is enabled whenever you see the filter buttons at the top of each Tabular array cavalcade. 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 Tabular array. When accessed, they display cavalcade-specific pull-down menus from which y'all tin can fix a filter. For near Table filtering, this might exist all you lot need. However, when you desire to perform more complex filtering, or create a re-create of your filtered information, you should use Advanced Filter below.

Activating Table Filters

Select a cell within the Table.
From the Home command tab, in the Editing grouping, click Sort & Filter » select Filter .
OR
From the Data command tab, in the Sort & Filter grouping, click Filter.
AutoFilter buttons announced at the top of each column of the selected Table.

Running Table Filters

Activate Tabular array Filtering.
In the column you want to filter, click the .
The Tabular array filter pull-down list appears, including a submenu of column-specific records you can use to filter your tabular array.
Annotation: By default, all records are selected (i.e., set up to display).
To filter the selected column, deselect the records you exercise non want displayed (i.e., be certain that but the records you want displayed are selected).
Click OK.
All rows fitting the criteria of the selected column are displayed.
NOTES:
When yous employ AutoFilter within a Tabular array, the row numbers of the displayed records plough blue, and the filter results appear in the status bar (e.chiliad., 1 of 12 records institute).
The button at the top of the column changes to
To remove the filter from your Table, in the filtered cavalcade, 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 lot desire to filter, click the » select Text Filters or Number Filters » Custom Filter…
The Custom AutoFilter dialog box appears.
NOTES:
If a cavalcade 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 club in the Custom AutoFilter pull-down list.
In the Comparing Operator pull-down list, select a blazon of comparison.
Example: Select is greater than .
In the Corresponding pull-downwardly list, select or type a criteria value.
EXAMPLE: Type 300 .
(Optional) If you desire multiple criteria, select either And or Or and repeat steps 3 and four.
Case:
In the Comparing Operator pull-down listing, select is less than.
In the Corresponding pull-downwards 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 inside the Table.
From the Dwelling house command tab, in the Editing group, click Sort & Filter » deselect Filter .
OR
From the Information command tab, in the Sort & Filter grouping, click Filter.
AutoFilter is disabled; the AutoFilter buttons are removed from the Table.

Using Advanced Filter

Excel's Avant-garde Filter has advantages not offered by the standard filter, such as its complex "and/or" filtering options. It also lets you lot move filtered Table information to a different expanse of the electric current worksheet.

Before You Showtime

Creating a criteria range
A criteria range consists of at least two rows. The commencement row must comprise a cavalcade characterization, the other must provide a filtering condition.

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

Additional filtering conditions tin can exist established in subsequent rows, allowing yous to plant a complex filter. At to the lowest degree one bare row must split up your Table from your criteria range. For more data on criteria, refer to Establishing Criteria.

Running an Avant-garde Filter

Create a criteria range inside your worksheet.
Select any cell within your Table.
From the Information command tab, Sort & Filter group, click Advanced Filter.
The Advanced Filter dialog box appears.
If you want the filter to supersede the current Table, select Filter the list, in-identify .
NOTE: If you do not want the filter to replace the electric current Table, refer to Copying an Advanced Filter to Another Location beneath.
In the Listing range text box, blazon the cell range containing your Table.
OR
To minimize the Advanced Filter dialog box and then you tin manually select your Table range,
1. Click Plummet Dialog.
2. Select your Table range.
3. Click Restore Dialog.
In the Criteria range field, type the cell range (or range proper noun) containing the criteria.
OR
To minimize the Avant-garde Filter dialog box so you tin can manually select prison 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 Avant-garde Filter

To remove an Avant-garde Filter from your Table:

From the Information 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 inside your worksheet.
Select a cell within your Table.
From the Data command tab, click Avant-garde Filter.
The Avant-garde Filter dialog box opens.
Select Copy to some other location.
In the List range text box, type the cell range containing your Table.
OR
To minimize the Advanced Filter dialog box and then y'all tin 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 prison cell range (or range name) containing the criteria.
OR
To minimize the Avant-garde Filter dialog box and so you can manually select the prison cell range,
1. Click Plummet Dialog.
2. Select the criteria range.
3. Click Restore Dialog.
In the Copy to text box, type a cell or range in the agile worksheet where the filter results will announced.
OR
To minimize the Advanced Filter dialog box so you can manually select a jail cell or range,
1. Click Collapse Dialog.
2. Select the cell or range.
3. Click Restore Dialog.
Click OK.