Excel 2010 Just the Steps For Dummies
Book image
Explore Book Buy On Amazon
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.

Make sure the AutoFilter is turned off.

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

Select the first four rows of the worksheet.

This is where you will insert the criteria range.

Right-click the selected rows and choose Insert.

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

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

A marquee appears around the copied area.

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

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

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

Enter any additional filter criteria.

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

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

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

Excel displays the Advanced Filter dialog box.

Select 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).

Verify the table range in the List Range box.

Make sure Excel recognizes the entire table.

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

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

Click OK to display the search results.

Click OK to display the search results.

The tables displays the filtered results. The records that don’t fit the criteria are hidden.

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

All the hidden records reappear.

About This Article

This article is from the book:

About the book author:

Diane Koers owns and operates All Business Service, a software training and consulting business formed in 1988, that services the central Indiana area. Her area of expertise has long been in the word-processing, spreadsheet, and graphics areas of computing. She also provides training and support for Peachtree Accounting Software. Diane's authoring experience includes over 40 books on topics, such as PC security, Microsoft Windows, Microsoft Office, Microsoft Works, WordPerfect, Paint Shop Pro, Lotus SmartSuite, Quicken, Microsoft Money, and Peachtree Accounting. Many of these titles have been translated into other languages, such as French, Dutch, Bulgarian, Spanish, and Greek. She has also developed and written numerous training manuals for her clients.
Diane and her husband enjoy spending their free time fishing, traveling, and playing with their four grandsons and their Yorkshire Terrier.

This article can be found in the category: