Filtering Data in Excel 2007

by Helen Bradley

Helen Bradley covers tips and tricks for using Excel's Filter tool as well as some of the new filtering features available 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.

» Saving a filter

There is no built-in option for saving a custom filter, so you will need to build each filter manually each time you want to apply it to your data. There is a workaround though that can help in some circumstances — you can save the filtered list as a View.

To do this, click the View tab on the Ribbon and then click the Custom Views button. Click Add and type a name for your view — for example, "owners and managers," and then click OK.

Later, when you need to see your data return to the View tab, click Custom Views, select the view you just created, and click Show. The screen will adjust to show the filtered data — another benefit is that the filter will also have been reapplied so changes to the data will be accounted for.

» New Filter Options

New to Excel 2007 is the ability to filter data based on cell color. So, for example, where you have used conditional formatting to color a range of cells different colors according to the values in the cells, you can filter on these colors. To do this, click the Filter button for the column containing the colored cells and choose Filter by Color and then select the color to filter on.

Excel 2007 also has new Date filtering options. Where you have a column containing dates, you will see that, instead of listing all the dates in the dialog you can filter by year or month or day as desired. You can also use the Date Filters option and select from a number of preset date options such as This Month, Last Month, Year to Date and so on.

» Copying Filtered Data

When you have filtered the data in a worksheet you will encounter problems if you try to copy the data. All the cells are selected and copied by default, not just those which are visible.

To select and copy only the visible cells, select the area to copy, and from the Home tab on the Ribbon, click the Find & Select button, select Go to Special > Visible Cells Only, and then click OK. Now when you copy and paste the data only the cells which were visible on the worksheet will be copied.

Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. She blogs at http://www.projectwoman.com/blogger.html.

Adapted from winplanet.com.

Do you have a comment or question about this article or other small business topics in general? Speak out in the SmallBusinessComputing.com Forums. Join the discussion today!

This article was originally published on Friday Oct 31st 2008
Mobile Site | Full Site