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:
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.
| NOSTALGIA CORNER |
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:
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 .
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.
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 .
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.
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.
Fill in the information for the first sort key in the Column, Sort On, and Order columns .
Figure 14-10 shows how it works.
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.
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 .
Click OK .
Excel sorts your entire table based on the criteria you've so carefully specified (Figure 14-12).
| POWER USERS' CLINIC |
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 184.108.40.206 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.
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.
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.
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.
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.
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).
| WORKAROUND WORKSHOP |
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.
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.