Using Advanced Filtering in Excel 2007

By Diane Koers

You can specify complex filtering criteria in Excel 2007 and designate a specific area of your worksheet to manage your criteria selections when filtering table data by using advanced filtering methods. Follow these steps to use advanced filtering:

1Make sure the AutoFilter is turned off (click the Filter button on the Data tab, if necessary).

There’s nothing “auto” about advanced filtering.

2Select the first four rows of the worksheet.

This is where you will insert the criteria range.

3Right-click the selected rows and choose Insert.

Your table moves down four rows, and you get four empty rows to work with.

4Select the header row in the table and click the Copy button on the Home tab.

A marquee appears around the copied area.

An excel spreadsheet with a filtering criteria range in the first row.

5Click cell A1, and then click the Paste button on the Home tab.

This copies the header row of your table to the first blank row in the worksheet. You now have a criteria range ready to enter filter selections.

Entering the filtering criteria in the first row of the spreadsheet.

6In the first blank row of the criteria range, enter the data you want to match.

For example, if you want to locate any entries for the state of Indiana, type IN directly under the State heading.

Filtering an Excel spreadsheet with two or more criteria.

7Enter any additional filter criteria.

If you want Excel to find data that meets more than one restriction, enter the desired additional criteria in another field on the first criteria row. This is called an AND filter. If you want Excel to find data that meets at least one of the criteria (an OR filter), enter the filter data on the second row of the criteria range.

8Click any cell in the main part of the table.

You shouldn’t have any of your filter criteria selected before you begin the next step.

9Click the Advanced button in the Sort & Filter group of the Data tab.

Excel displays the Advanced Filter dialog box.

10Select the Filter the List, In-Place option in the Action section.

If you want the filter results to appear in another location in the current worksheet, select Copy to Another Location instead (and then specify the location in the Copy To text box).

11Verify the table range in the List Range box.

Make sure Excel recognizes the entire table.

12Specify the criteria range, including the header row, but not any blank rows.

You can type the range address or drag, starting from cell A1, to select the range in the worksheet. Be sure to specify only the rows that contain filtering information. If you include blank rows in your criteria range, Excel includes them in the filtering process. The effect is that no data is filtered out, so all records are returned.

Excel displays the results of a search done using advanced filtering.

13Click OK to display the search results.

The records that don’t fit the criteria are hidden.

14When you’re ready to view all data records again, click the Clear button on the Data tab.

All the hidden records reappear.