Using Advanced Table Filtering in Excel 2010

By Diane Koers

With Excel 2010’s advanced filtering methods, you can specify complex filtering criteria and designate a specific area of your worksheet to manage your criteria selections when filtering table data; for example, you can filter by date. Use the following directions to filter data that has been formatted as a table in Excel 2010.

1Make sure the AutoFilter is turned off.

Click the Filter button on the Data tab, if necessary.

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.

Copy and pasting content on a spreadsheet.

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.

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.

Setting filter criteria on Excel.

7Enter any additional filter criteria.

If you want Excel to find data that meets more than one restriction, enter the 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.

Specifying criteria range in an Excel worksheet.

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

You can type the range address or drag 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.

A filtered Excel worksheet.

13Click OK to display the search results.

The tables displays the filtered 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.