How To Create Filter In Excel 2007

One typical application for Excel is to find information in a list of data. The simplest way to do this is filtering the data so that Excel does the work of finding the pertinent information for you rather than you having to do this manually.

The filtering tools in Microsoft Excel 2007 are more sophisticated than those available in earlier versions. In this article we'll show you how to find and use Excel's Filter tool and some of its new features in Excel 2007.

» Filter Basics

By default worksheet data is not filtered automatically unless you format the data as a table. To do this, select the data, click the Home tab, select Format as Table and choose a table format. When you do this, your list is automatically created as a filtered list with filter indicators in the top row of the table to the immediate right of the column headings.

If you don't want to set up your data as a table, you can still filter it by clicking somewhere in the data and, from the Home tab, selecting the Sort and Filter button and then clicking Filter. Alternately, you will find a Filter button (and some useful other filter options) on the Data tab on the Ribbon. When you choose the Filter button, Excel automatically detects the block of values surrounding the current cell and creates the filter buttons for you.

To filter the data, select the column to filter on to display, for example, only those entries related to a particular State. To start, first click the column heading for the column containing the State details. Next, click the down-pointing arrow to the right of the heading, deselect the Select All option, and then select the checkboxes for the states to view from the list. Click OK, and only data matching your query will be displayed.

When there is a filter in place on a worksheet you will see an indication of this in the icon beside the column that contains the filter. The icon shows a picture of a filter rather than a down-pointing arrow and if you hold your mouse pointer over the filter indicator it shows details of the filter being applied. To return to viewing all the data, click the Clear button on the Data tab on the Ribbon.

» More Complex Filters

More complex queries are almost as easy to create. For example, if you have a column containing an employee's title and you want to display all owners as well as people whose titles include the designation 'manager,' you can easily do so.

First, click the filter arrow alongside the Title heading and choose Text Filters > Equals. The word Equals will appear in the first box in the dialog. From the dropdown list to its immediate right, select Owner. Select the option for "Or" because you want to find people who are either Owners or Managers.

From the second set of boxes, select "Contains" because you want to find anyone whose title contains the word manager — it might not be the only word in their title and it might not be the first word, so "Contains" is the best option to use.

In the box to the right, type "Manager" and click OK. Now the list will show only those people who are either owners or whose title includes the word manager.

» Updating a Filter

If you have filtered your data and you then change a value in a cell affected by the filter the filter will not be reapplied to this data. This lets you dynamically alter entries in the filtered list more easily.

When you're finished and you want to see how the list looks when filtered with the original query, click the Reapply button on the Data tab of the Ribbon and the filter will be reapplied to the changed data.