Filtering Data in Excel 2007

By Helen Bradley | Posted October 31, 2008

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.



Page 1 of 2

 
1 2
Next Page

Your White Papers Search Results

  • eBook
    The Truth About Cloud Security
    "Security" is the number one issue holding business leaders back from the cloud. But does the reality match the perception? Keeping data close to...
    Download now
  • eBook
    The Threat Landscape
    Hardly a day goes by without the discovery of a new cyberthreat somewhere in the world! But how do you keep up with new malware and evolving...
    Download now

Comment and Contribute


     

    Explore our site

    Get free tips, news and advice on how to make technology work harder for your business.

    Submit
    Learn more
     
    You have successfuly registered to
    Enterprise Apps Daily Newsletter
    • webcast video
      Microsoft Publisher Tips This video shows you how to create great-looking business brochures with Microsoft Publisher 2003.
    • webcast video
      Photoshop Tips In this video, we show you how to improve on or eliminate ugly and unwanted backgrounds.