ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Filtering and Grouping data in Excel 2007

Updated on August 22, 2012

Introduction

Hi and welcome to my hub on using filters and groups to organise your data in Excel 2007. Using filters will allow you to sort your data based on any characteristic of your data that you desire. You can use any one of the numerical filters available; Excel 2007 has everything from averages and a top ten to greater than or less than. In addition, you can filter text using and / or statements using for example, contains or ends with. Filters allow you to drill down into your data with ease and find records that fit any criteria you choose.

Grouping allows data to be tied together in groups. These groups can be expanded or collapsed and sub-groups can also be created within groups. This allows very large data sets to be organised and summarised in a structured manner to make the data easier to understand by effectively hiding data within groups. An example of where grouping is very useful is for inventories. If you had an inventory of all software installed on every computer in your business, grouping the software installed first by vendor for example Adobe, then by application for example Adobe Acrobat, then by version would allow you to collapse a large amount of data into meaningful and useful groups. It would be visually very easy to find which computers in your organisation have Adobe Acrobat 9.0 installed using grouped data.


Configuring Filtering

The configuration of filtering is very straightforward. Firstly, select the data you wish to have filters applied to and go to the Data tab and then select Filter. There is also Advanced to the right of the filter button which allows you to copy the data to another location should you wish to.

Once you apply filtering to your data, Excel will add drop down boxes to the top of each column to allow you to filter data based on the data in the column selected (as shown below). You may also need to resize the columns to display the data in the columns as well as the drop down boxes.

Applying a numerical filter

To apply a filter to your data, select the drop down box that corresponds to the data you wish to use as the basis for your filter. In my case, I wish to know which of my hubs have a hub score of 70 or above. I select the drop down box; click on Number Filters and then choose Greater than. I then enter 70 into the right hand box and click OK.

The drop down box will then change to indicate that a filter has been applied to that row. To clear a filter, click on the Clear button to the right of the Filter button on the Data tab.

Further sorting can be done using the Sort Smallest to Largest and Sort Largest to Smallest buttons to the left of the Filter button.

Applying a Text Filter

As well as filtering using numerical filters, you can also use text based filters. If I want to list all my hubs with Tung Chung in the title, I can do this using text filters. Select the drop down box of your column containing text and select Text Filter and then Contains. Type whatever text you want to filter by in the box to the right of contains and click OK. This will then filter your data based on the text you specified.

Grouping Data

Before actually grouping the data, it is important to have a clear understanding of how you want the data to be grouped and ensure that the data is ordered correctly to allow that grouping. The reason for this is that once grouped, data has to be ungrouped before it can be moved. For my example, I will be grouping data taken during a software inventory.

In addition, to keep the titles of each group clean, it is a good idea to put a blank record with just the title at the top of each row as shown below. To illustrate this, I have my spreadsheet before and after this formatting below (I didn’t create labels for Version to ensure it fitted onto one screen).

Once your data is in ordered correctly, the actual grouping is very straightforward.

IMPORTANT: There are a few things to bear in mind when creating your groups.

  • When grouping work from right to left.
  • Select all the items in your columns (or rows) that you wish to group excluding the top (or left most item). These will become the label of your group.

To begin grouping my data, I begin with grouping the Version column, then the Software Application column before finishing with the Software Vendor column remembering not to use the top item as it will become the label. Once I have created all my groups, the spreadsheet looks like this…

Once the data is grouped, anyone who uses the spreadsheet can choose which data that want to drill down to and effectively ignore the rest. If someone wants to know just which PCs have Adobe Illustrator 5 installed, they just need to open those groups to find that out. All the data pertaining to other vendors or to other Adobe software applications can easily be ignored by simply not expanding the groups.

Ungrouping data

To ungroup data, simply select the data you wish to ungroup and select the Ungroup button on the Data tab. Excel 2007 will ungroup the data starting with the groups on the left working right each time you click it.

Conclusion

In this hub, I have explored using the Filter and Group functions in Excel 2007.

Filtering allows you to display only data that meets the criteria (both numerical and text based) that you chose. This allows you to perform further calculations on subsets of your data easily.

Grouping allows you to organise your data into groups so that very large amounts of data (for example a software inventory) can be organised and then collapsed into groups containing similar identifying factors (such as a group for all software from Adobe). A user of your document will see a list of vendors each of which contains groups of software and so on, rather than screens and screens of data.

I also have a number of other hubs on aspects of Excel 2007, covering everything from Conditional Formatting to creating charts and graphs. I have an Index hub which also covers how I successfully transitioned from Excel 2003 to 2007 as well as outlining my other Excel 2007 hubs which can be found here

http://robbiecwilson.hubpages.com/hub/How-to-adjust-to-Excel-2007-from-previous-versions-as-well-as-step-by-step-guides-to-many-functions-in-Excel-2007

I hope that you found this hub useful. Good luck with your adventures using Excel 2007! Please feel free to leave any feedback or comments you may have below.

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)