Section 14.2. Sorting and Filtering a Table

14.2. Sorting and Filtering a Table

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

  • Sorting lets you order the items in your table 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 table.

  • Filtering lets you display only certain records in your table based on specific criteria you enter. Filtering lets you 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 table.

Note: Don't see a drop-down list at the top of your columns ? A wrong ribbon click can inadvertently hide them. If you just see ordinary column headings (and you know you have a bona fide table), choose Data Sort & Filter Filter to get the drop-down lists back.
The Data Form

In previous versions of Excel, you could edit tables using another tool: the data form . Essentially, the data form is another window that pops up when you want it, and shows you every column of information for a single row. Using the data form, you can step from one row to another, search for specific rows, and change their values.

Most Excel fans found the data form interesting but not particularly useful. They mostly used it with very wide tables. Even if your columns stretch to the right side of the Excel window, the data form can usually pack them all into view in a single window. The data form's chief drawback is that it shows one record at a time, which is a bit limiting.

In Excel 2007, the data form is missing in action. You can't summon it from the ribbon. However, if you have a secret craving to get it back, there's a wayyou can add it to the Quick Access toolbar.

Here's what you need to do:

  1. Right-click the Quick Access toolbar, and then choose Custom Quick Access Toolbar. This toolbar opens the Excel Options window, and then sends you to the right place (the Customize section).

  2. In the "Choose commands from" list, select "Commands Not in the Ribbon" to see those commands that Excel 2007 has hidden from sight.

  3. In the list underneath, select Form. (The list is arranged alphabetically.)

  4. Click the Add button to move the Form command to the list on the right, which details all the commands that are currently in the Quick Access toolbar.

  5. Click OK. Now you can move inside a table, and then click Form to pop open the data form, just like in earlier versions of Excel.

14.2.1. Applying a Simple Sort Order

Before you can sort your data, you need to choose a sorting key the piece of information Excel uses to order your records. For example, if you want to sort a table of products so the cheapest (or most expensive) products appear at the top of the table, 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 may 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 the first character 4 comes after 1 .

To apply a new sort order, 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 then choose one of the menu commands that starts with the word "Sort." The exact wording depends on the type of data in the column, as follows :

  • If your column contains numbers , you see "Sort Smallest to Largest" and "Sort Largest to Smallest".

  • If your column contains text , you see "Sort A to Z" and "Sort Z to A" (see Figure 14-9).

  • If your column contains dates , you see "Sort Oldest to Newest" and "Sort Newest to Oldest".

When you choose an option, Excel immediately reorders the records, and then places a tiny arrow in the column header to indicate that you used this column for your sort. However, Excel doesn't keep re-sorting your data when you make changes or add new records (after all, it would be pretty distracting to have your records jump around unexpectedly). If you make some changes and want to reapply the sort, just go to the column header menu and choose the same sort option again.

If you click a second column, and then 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.

14.2.2. Sorting with Multiple Criteria

Simple table sorting runs into trouble when you have duplicate values. Take the product table sorted by category in Figure 14-9, 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 a bunch of products in the Communications category, then they appear in whatever order they were in on your worksheet, which may not be what you want. In this case, you're better off using multiple sort criteria .

Figure 14-9. 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 automatically provides them for every table.

With multiple sort criteria, Excel orders the table 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.

  1. Move to any one of the cells inside your table, and then choose Home Editing Sort & Filter Custom Sort .

    Excel selects all the data in your table, and then displays the Sort dialog box (see Figure 14-10) where you can specify the sorting keys you want to use.

    Note: You can use the Home Editing Sort & Filter Custom Sort command with any row-based data, including information thats not in a table. When you use it with non-table data, Excel automatically selects the range of cells it believes constitutes your table.
  2. Fill in the information for the first sort key in the Column, Sort On, and Order columns .

    Figure 14-10 shows how it works.

    Figure 14-10. To define a sorting key, you need to fill in the column you want to use (in this example, Category). Next , pick the information you want to use from that column, which is almost always the actual cell values (Values). Finally, you need to choose the order for arranging values, which depends on the type of data. For text values, as in this example, you can pick A to Z, Z to A, or Custom List (Section

  3. If you want to add another level of sorting, click Add Level, and then follow the instructions in step 2 to configure it .

    You can repeat this step to add as many sorting levels as you want (Figure 14-11). Remember, it makes sense to add more levels of sorting only if there's a possibility of duplicate value in the levels you've added so far. For example, if you've sorted a bunch of names by last name, you want to sort by first name, because some people may share the same last name. However, it's probably not worth it to add a third sort on the middle initial, because very few people share the same first and last name.

    Figure 14-11. This example shows two sorting keys: the Category column and the Model Name column. The Category column may contain duplicate entries, which Excel sorts in turn according to the text in the Model Name column. When you're adding multiple sort keys, make sure they're in the right order. If you need to rearrange your sorting, select a sort key, and then click the arrow buttons to move it up the list (so it's applied first) or down the list (so it's applied later).

  4. Optionally, 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 switch it on, travel appears before Travel .

  5. Click OK .

    Excel sorts your entire table based on the criteria you've so carefully specified (Figure 14-12).

Figure 14-12. The worksheet shows the following sort's result: alphabetically ordered categories, each of which contains a subgroup of products that are themselves in alphabetical order.

Sorting with a Custom List

Most of the time, you'll want to stick with the standard sorting orders. For example, you'll put numbers in numeric order, dates in chronological order, and text in alphabetical order. But not always. For example, you may have good reason to arrange the categories in Figure 14-12 in a different order that puts more important categories at the top of the table. Or you may have text values that have special meaning and are almost always used in a specific nonalphabetical order, like the days of the week (Sunday, Monday, Tuesday, and so on) or calendar months (January, February, March, April, and so on).

You can deal with these scenarios with a custom list that specifies your sort order. In the Order column, choose Custom List. This choice opens the Custom List dialog box, where you can choose an existing list or create a new one by selecting NEW LIST and typing in your values. (Section has more on creating specialized lists.) Figure 14-13 shows an example.

Custom list sorting works best when you have a relatively small number of values that never change. If you have dozens of different values, it's probably too tedious to type them all into a custom list.

Figure 14-13. Using a custom list for your sort order, you can arrange your categories so that Travel always appears at the top, as shown here. Once you've finished entering a custom list, click Add to store the list for future use.

14.2.3. Sorting by Color

One of Excel's weirdest sorting options is sort-by-color, which lets you arrange your records based on the color of each cell. This oddball feature may come in handy if you've specifically highlighted certain cells that you're interested in (for example, you've given them a hot pink background fill). But this feature is really designed to work in conjunction with conditional formatting (Section 6.3.2). You can create a conditional format that changes the foreground or background color of the cells you're interested in. Then, you use color sorting to make sure those cells rise to the top.

You can do color-based sorting in two ways. The quickest option is picking out cells with one background color, and then sending them to the top. To sort this way, open the drop-down color list, choose Sort By Color, and then choose your color. Excel helps out by letting you pick from a list that has all the background colors that are applied to cells in the current column. (This list doesn't include the background color that's applied from the table style.)

If you want to pick out multiple colors, you need to create a custom sort, and create a separate sort key to pick out each color. So if you want to sort cells in the order red, green, yellow, and then no color, you need to add three sorting keys. Choose Data Sort & Filter Sort, and then add three rules, as shown in Figure 14-14. Using the Sort On column, you can choose to sort using the background color (choose Cell Color) or the font color (Font Color).

The custom sort feature also works with icon sets (Section 6.3.5) if you choose Cell Icon from the Sort On column. Same idea, but instead of picking a color that should rise to the top of the list, you pick one of the icons from your icon set.

Figure 14-14. In this table, rows with a red background appear at the top, followed by green and then yellow rows (followed by any rows that don't have a background color). If you want to send a color to the bottom of a table instead of the top, change On Top to On Bottom.

14.2.4. Filtering with the List of Values

Sorting is great for ordering your data, but it may not be enough to tame large piles of data. You can try another useful technique, filtering , which lets you limit the table so it displays only the data that you want to see. Filtering may seem like a small convenience, but if your table 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 pluck 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 a report that shows only the customers who 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 in Section 14.4.5, 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's 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 you can see them all at once, or print them out on a single sheet of paper.

Automatic filtering, like sorting, uses the drop-down column headings. When you click the drop-down arrow, Excel shows a list of all the distinct values in that column. Figures 14-15 and 14-16 show how filtering works on the Category column.

To remove a filter, open the drop-down column menu, and choose Clear Filter.

Figure 14-15. Initially, each value has a checkmark next to it. Clear the checkmark to hide rows with that value. (In this example, products in the Deception category won't appear in the table.) Or, if you want to home in on just a few items, clear the Select All checkmark to remove all the checkmarks, and then choose just the ones you want to see in your table, as shown in Figure 14-16.

Figure 14-16. If you select Communications and nothing else from the Category list in the product table example, the table displays only the five products in the Communications category.

14.2.5. Creating Smarter Filters

The drop-down column lists give you an easy way to filter out specific rows. However, in many situations you'll want a little more intelligence in your filtering. For example, imagine you're filtering a list of products to focus on all those that top $100. You could scroll through the list of values, and remove the checkmark next to every price that's lower than $100. What a pain in the neck that would be.

Thankfully, Excel has more filtering features that can really help you out here. Based on the type of data in your column (text, a number, or date values), Excel adds a wide range of useful filter options to the drop-down column lists. You'll see how this all works in the following sections. Filtering dates

You can filter dates that fall before or after another date, or you can use preset periods like last week, last month, next month, year-to-date, and so on.

To use date filtering, open the drop-down column list, and choose Date Filters. Figure 14-17 shows what you see.

Figure 14-17. Shown here is the mind-boggling array of readymade date filtering options you can apply to a column that contains dates. For example, choose Last Week to see just those dates that fall in the period Sunday to Saturday in the previous week. Filtering numbers

For numbers, you can filter values that match exactly, numbers that are smaller or larger than a specified number, or numbers that are above or below average.

To use number filtering, open the drop-down column list, choose Number Filters, and then pick one of the filter options. For example, imagine you're trying to limit the product list to show expensive products. You can accomplish this quite quickly with a number filter. Just open the drop-down column list for the Price column, and then choose Number Filters Greater Than Or Equal To. A dialog box appears where you can supply the $100 minimum (Figure 14-18).

Figure 14-18. This dialog box lets you complete the Greater Than Or Equal To filter. It matches all products that are $100 or more. You can use the bottom portion of the window (left blank in this example) to supply a second filter condition that either further restricts (choose And) or supplements your matches (choose Or).

The Disappearing Cells

Table filtering's got one quirk. When you filter a table, Excel hides the rows that contain the filtered records. For example, in Figure 14-17 you'll notice that the row numbers jump straight from 8 to 36, indicating that all the rows in between are hidden. In fact, all Excel really does is shrink each of these rows to have a height of 0 so they're neatly out of sight. The problem? When Excel hides a row, it hides all the data in that row, even if the data is not a part of the table .

That property means that if you place a formula in one of the cells to the right of the table, then this formula may disappear from your worksheet temporarily when you filter the table! This behavior is quite a bit different from what happens if you delete a row, in which case cells outside the table aren't affected.

If you frequently use filtering, you may want to circumvent this problem by putting your formulas underneath or above the table. Generally, putting the formulas above the table is the most convenient choice because the cells don't move as the table expands or contracts. Filtering text

For text, you can filter values that match exactly, or values that contain a piece of text. To apply text filtering, open the drop-down column list, and then choose Text Filters.

If you're performing filtering with text fields, you can gain even more precise control using wildcards. The asterisk (*) matches any series of characters , while the question mark (?) matches a single character. So 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.

Excel 2007[c] The Missing Manual
Excel 2007[c] The Missing Manual
ISBN: 596527594
Year: 2007
Pages: 173 © 2008-2017.
If you may any questions please contact us: