Section 13.3. Sorting and Filtering a List | Excel 2010: The Missing Manual

13.3. Sorting and Filtering a List

As you've seen, Excel data lists make it easier to enter, edit, and manage large collections of information. Now it's time to meet two of the most useful data list features:

Sorting lets you order the items in your list alphabetically or numerically according to the information in a column. By using the correct criteria, you can make sure the information you're interested in appears at the top of the column, and you can make it easier to find an item anywhere in your list.
Filtering lets you display only certain records in your list based on specific criteria you enter. Filtering gives you the ability to work with part of your data and temporarily hide the information you aren't interested in.

You can quickly apply sorting and filtering using the drop-down column headers that Excel adds to every list.

13.3.1. Applying a Simple Sort Order

Before you can sort your data, you need to choose a sorting key . The sorting key is the piece of information that Excel uses to order your records. For example, if you want to sort a list of products so that the cheapest products appear at the top of the list, the Price column would be the sorting key to use.

In addition to choosing a sorting key, you also need to decide whether you want to use ascending or descending order. Ascending order, which is most common, organizes numbers from smallest to largest, dates from oldest to most recent, and text in alphabetical order. (If you have more than one type of data in the same columnwhich is rarely a good ideatext appears first, followed by numbers and dates, then true or false values, and finally error values.) In descending order, the order is reversed .

Note: Remember, it's technically possible to have numbers in Excel that are stored as text, as described in Chapter 2. Simply prefix these values with an apostrophe ('). For example, you might store a number as text when you're entering numeric content that doesn't convey a numeric value, like a social security number. Excel sorts these values alphabetically, which means it looks at the text string one character at a time until it finds a difference. Thus, even though 42 is less than 102 in a numeric sort, the text 42 is greater than 102 in an alphabetic sort, because 4 comes after 1.

To apply a new sort order in Excel 2003, choose the column you want to use for your sort key. Click the drop-down box at the right side of the column header and choose Sort Ascending or Sort Descending, which are the first two options in the menu (see Figure 13-8). Excel reorders the records immediately.

Figure 13-8. A single click is all it takes to order records in ascending order by their category names . You don't need to take any action to create these handy drop-down listsExcel 2003 automatically provides them for every data list.

If you click a second column and choose Sort Ascending or Sort Descending, the new sort order replaces your previous sort order. In other words, the column headers let you sort your records quickly, but you can't sort by more than one column at a time.

Note: Excel 2002 doesn't include a simple sort feature. However, you can still use sorting with the approach described in the next section, "Sorting with Multiple Criteria."

13.3.2. Sorting with Multiple Criteria

Simple data list sorting runs into trouble when you have duplicate values. Take the product list sorted by category in Figure 13-8, for example. All the products in the Communications category appear first, followed by products in the Deception category, and so on. However, Excel doesn't make any effort to sort products that are in the same category. For example, if you have 20 products in the Communications category, they appear in whatever same order as they were in on your worksheet, which might not be what you want. In this case, a better solution is to use multiple sort criteria .

With multiple sort criteria, Excel orders the list using more than one sorting key. The second sorting key springs into action only if there are duplicate values in the first sorting key. For example, if you sort by Category and Model Name, Excel first separates the records into alphabetically ordered category groups. It then sorts the products in each category in order of their model name .

To use multiple sort criteria, follow these steps. This approach is also the ticket for performing any type of sort with a list in Excel 2002.

Move to any one of the cells inside your data list, and then choose Data Sort.

Excel selects all the data in your list and displays the Sort dialog box (see Figure 13-9) where you can specify the sorting keys you want to use. The Sort dialog box lets you set up to three keys. You can choose each key from a drop-down list box that includes all of the column headers.

Note: You can use the Data Sort command with any row-based data, including information that's not in a data list. When you use it with non-list data, Excel automatically selects the range of cells it believes constitutes your table.
In the "Sort by" text box, select the field that you want to use for the first sort key. Then, choose a radio button to specify an ascending or descending sort.

If there's a possibility that this field contains duplicates, you'll want to continue to the next step to define a secondary sorting key. In this example (Figure 13-9), the first sort key is Category, and it would make sense to further sort the items within each category (all the Communications items, all the Deception items, and so on).

In the "Then by" text box (under the first "Sort by" text box), choose a second sort key to apply in the case of duplicates.

In this example, the second sort key is Model Name. If you'd like to use a third sort key, choose it in the second "Then by" text box.

Figure 13-9. Top : In this example, there are two sorting keys: the Category column and the Model Name column. The Category column might contain duplicate entries, which in turn get sorted according to the text in the Model Name column.
Bottom : The worksheet shows the result of this sort: alphabetically ordered categories, each of which contains a subgroup of products that are themselves in alphabetical order.

You can click the Options button to configure a few finer points about how your data is sorted. For example, you can turn on case-sensitive sorting, which is ordinarily switched off. If you do so, then travel will appear before Travel . You can also tell Excel to sort according to a defined list. This technique is useful for sorting items like the days of the week (Sunday, Monday, Tuesday, and so on), which shouldn't use the ordinary alphabetic sorting order. (For a refresher on custom lists and how to create them, see the AutoFill information on Section 2.2.3.)
Click OK.

Excel sorts your entire list based on the criteria you've so carefully specified.

FREQUENTLY ASKED QUESTION
Using Multiple Sort Keys

Can I sort using more than three sorting keys?

Yes. Excel can accommodate you in those rare circumstances where you need to sort using more than three sorting keys. Just perform more than one sort operation! The trick is to understand the order you need to follow when performing multiple sorts.

The rule of thumb is to sort by the least important information first. For example, imagine a list that you want to sort by Category, First Name, Last Name, and then Age. In this case, sort first by Age. Then, perform another sort operation that uses the Category, First Name, and Last Name sorting keys. Any records that have duplicates in all these fields remain in their current order. In this example, they'd stay in age order because that's the sorting order you applied in the last operation.

Remember, it makes sense to use four sorting keys only if it's possible for duplicate values to exist in the first three sorting keys. Even then, it may not be that important unless you have to distinguish between a really large number of records.

13.3.3. Applying an Automatic Filter

Sorting is great for ordering your data, but it might not be enough to tame large piles of data. Another technique that can help is filtering , which lets you limit the list so it displays only the data that you want to see. Filtering may seem like a convenience, but if your list contains hundreds or thousands of rows, filtering is vital for your day-to-day worksheet sanity . Here are some situations where filtering becomes especially useful:

To weed out important information, like the number of accounts that currently have a balance due. Filtering lets you see just the information you need, saving you hours of headaches .
To print out a report that only shows the customers that live in a specific city.
To calculate information like sums and averages for products in a specific group . You can use a function like SUBTOTAL( ), described later in this chapter, to perform calculations using only the cells that are currently visible.

In theory, you could use the search feature in the data form window to find the records that interest you. However, while the search feature is perfect for finding one or two matches in a sea of data, it's much less suited to finding a large number of rows, because there's no way to see them all at once or print them out on a single sheet of paper.

Note: To use filtering with Excel 2002, you need to click in your list and select Data

Filter

AutoFilter. This trick adds the drop-down column headers you need.

Filtering, like sorting, uses the drop-down column headings. You can choose to restrict rows in several ways (see Figure 13-10):

By a specific column value . For example, if you select Communications from the Category field in the product list example (Figure 13-10), the list shows only the five products that are in the Communications category.

Figure 13-10. Excel automatically inserts all the distinct values from a column into the drop-down column list.

By limiting columns to the top 10 values . This option works only for numeric columns. For example, if you choose "(Top 10...)" under the Price field in the product list, you'll see the 10 most expensive products. The "(Top 10...)" option is actually more flexible than it seems. When you select it, you'll see a dialog box where you can choose the number of items to display, which can be more or less than the standard option of 10. You can also choose to display only the bottom items (those with the smallest values).
By limiting the columns with a custom condition . Just select "(Custom...)" from the column header and enter your filter criteria. You'll learn more about this technique in the next section.

Filters are cumulative, so that if you add a filter condition to two columns, you see only the rows that match both those conditions. To remove a filter condition, select "(All)" from the drop-down column header.

13.3.4. Creating a Custom Filter

To gain even greater control over how your records are filtered, you can create a custom filter condition. A custom filter condition fits the bill if you want to filter records that:

Fall under or above a set value
Fall within a range of values
Match one of two conditions

To create a custom filter condition, follow these steps:

From the drop-down column list, choose the column you want to use for filtering, and select "(Custom...)".

The Custom AutoFilter dialog box appears (Figure 13-11). Using this dialog box, you can specify up to two filtering criteria.

Figure 13-11. In this example, records must match two filter conditions and have prices that fall between $50 and $1000.
From the first drop-down list box, choose the type of comparison you want to perform.

You'll find this list includes all the familiar options, including equal to, not equal to, greater than, and so on.
In the list box immediately to the right, enter the value you want to use for comparison.

You can type the value in manually, or you can choose one of the options from the list.
If you want, you can specify a second filter criteria. To do so, click the And or Or radio button to specify how to apply this filter criteria. Then, repeat steps 2 and 3 for the second set of list boxes.

If you choose And, then records must match both filter conditions to get displayed. If you choose Or, records get displayed as long as they match either one of the filter criteria.
Click OK to apply the filter.

To change your custom filter, just repeat these steps and specify new criteria. To remove the filter, just select "(All)" from the drop-down column list.

Tip: Remember, you can apply a custom filter condition to more than one column at once for even more stringent filtering.

If you're performing filtering with text fields, you can gain even more precise control using wildcards. The asterisk (*) is used to match any series of characters , while the question mark (?) matches a single character. That means the filter expression Category equals T* matches any category that starts with the letter T. The filter expression Category equals T???? matches any five-letter category that starts with T.

Note: You might notice that the way filtering works is different than the way search criteria works when using the data form wizard. In the data form wizard, partial text matches are always accepted. With custom filters, partial text matches are rejected, unless you explicitly add the asterisk to the end of your filtering criteria.