Sorting, Grouping, and Filtering Cells

The Excel sort function and Excel filter function are highly useful when working with large amounts of data.

Introduction

Sorting, Grouping, Filtering A Microsoft Excel spreadsheet can contain a great deal of information. With more rows and columns than previous versions, Excel 2007 gives you the ability to analyze and work with an enormous amount of data. To most effectively use this data, you may need to manipulate it in different ways.

In this lesson, you will learn how to sort, group, and filter data in various ways that will enable you to most effectively and efficiently use spreadsheets to locate and analyze information.

Sorting, grouping, and filtering

A Microsoft Excel spreadsheet can contain a great deal of information. Sometimes you may find that you need to reorder or sort that information, create groups, or filter information to be able to use it most effectively.

Sorting

Sorting lists is a common spreadsheet task that allows you to easily reorder your data. The most common type of sorting is alphabetical ordering, which you can do in ascending or descending order.

To sort in alphabetical order:

Sorting

You can Sort in reverse alphabetical order by choosing Sort Z to A in the list.

To sort from smallest to largest:

You can sort in reverse numerical order by choosing From Largest to Smallest in the list.

To sort multiple levels:

Sort from Data Tab
Custom Sort Dialog Box
Add Level
Sort 2nd Level

The spreadsheet has been sorted. All of the categories are organized in alphabetical order, and within each category the unit cost is arranged from smallest to largest.

Remember that all of the information and data is still here—it's just in a different order.

Grouping cells using the Subtotal command

Grouping is a useful Excel feature that gives you control over how the information is displayed. You must sort before you can group. In this section, we will learn how to create groups using the Subtotal command.

To create groups with subtotals:

Subtotal
Subtotal Example

To collapse or display the group:

Outline Group Commands

To ungroup select cells:

To ungroup the entire worksheet:

Filtering cells

Filtering, or temporarily hiding, data in a spreadsheet is simple. This allows you to focus on specific spreadsheet entries.

To filter data:

Filter
Filter Records

To clear one filter:

Clear Filter

To remove all filters, click the Filter command.

Filtering may look a little like grouping, but the difference is that now you can filter on another field if you want to. For example, let’s say you want to see only the vanilla-related flavors. Just click the drop-down arrow next to Item, then select Text Filters. From the menu, choose Contains because you want to find any entry that has the word vanilla in it. A dialog box appears. Type vanilla, then click OK. Now we can see that the data has been filtered again and that only the vanilla-related flavors appear.

Challenge!

Use the Inventory workbook or any workbook you choose to complete this challenge.