Excel provides numerous ways to sort worksheet ranges. You can use the same techniques to sort both tables and ranges that you have not defined as tables. (We’ll call the latter lists.)
You can sort by column or row, in ascending or descending order, and with capitalization considered or ignored. (When you sort by row, your rows are rearranged, and the columns remain in the same order. When you sort by column, the opposite kind of rearrangement occurs.) You can even define custom sorting sequences so that, for example, your company’s division names always appear in a particular order, regardless of their alphabetic sequence. Excel 2010 can sort by as many fields as you want, not just the three-at-a-time limit of some previous versions. And, you can sort by using the format of the cells, not just the value. Sorting a table is essentially the same as sorting a simple list in the worksheet. Having headings at the top of the range is helpful but not necessary.
To sort on a single column—the Last Name column in Figure 22-4, for example—select one cell anywhere within that column. Then click either the Sort A To Z button in the Sort & Filter group on the Data tab (to arrange the column in ascending numeric or alphabetic order) or the Sort Z To A button (to do the opposite). Excel sorts in the order you want on the column in which the selection resides. If you don’t want to switch to the Data tab on the ribbon, right-click a cell, and then click the appropriate sort command on the Sort menu.
Figure 22-4. One easy way to sort on a single column is to right-click a cell in the column and choose Sort.
When you click one of the Sort buttons, Excel assumes you want to sort the rows. If you’re sorting a table, the table definition determines whether the first row holds headers (and should not be sorted) or data (and should be sorted). If you’re sorting a list, Excel guesses whether the first row includes headers or not. If the quick command version doesn’t meet your needs, you need to use the Sort dialog box.
To use the Sort dialog box, click the Sort button in the Sort & Filter group on the Data tab. (It’s also available when you right-click a cell in the range you want to sort.) If this is the first time you’ve sorted the current range, the Sort dialog box, shown in Figure 22-5, appears. If you’ve sorted the range before, the dialog box displays the sort parameters you last used.
Figure 22-5. In the Sort dialog box, you can specify the field to sort by, the order to sort by, and whether to sort on values, colors, or icons.
If your data includes a header row that should remain in place while the other rows are sorted, Excel usually recognizes that fact and selects the My Data Has Headers check box. If Excel, for some reason, fails to notice a header row or if it detects a header row when one isn’t really there, you can correct it before clicking OK.
You can sort on as many columns as you want. To sort on more than one column, click the Add Level button in the Sort dialog box. For example, to sort the staff list shown in Figure 22-4 first in descending order by salary and then in ascending order by last name, you fill out the dialog box as shown in Figure 22-6. Excel then rearranges the list as shown in Figure 22-7.
If you sort a table, Excel always sorts the entire table, regardless of how many cells within the table you initially select.
In a regular range, if you select a single cell before sorting, Excel scans the area surrounding the selected cell, highlights the entire contiguous range of cells, and assumes you want to sort that entire range. If you want to sort only part of a range, start by selecting only those rows and columns you want to sort. Then click Data, Sort. To sort rows 10 through 20 in Figure 22-4, for example, you start by selecting A10:G20. If you select one column from something that appears to be a list, Excel asks you whether you really do want to sort just that one column or whether you want to expand the selection to include the entire list. Most of the time, you’ll probably want to sort the entire list, so either convert the list to a table or be sure to select only a single cell before you sort the list.
You can’t specify a sort range in the Sort dialog box. You must select the range before you open the dialog box. The dialog box doesn’t indicate the range Excel is about to sort. Check your worksheet immediately after a sort, and use the Undo command if you don’t like what you get.
Thus far, our examples have involved sorting by row—leaving the columns alone. You also can sort by column, leaving the order of the rows alone. If you have turned a list into a table, you cannot sort by column. This makes sense because a table is always row oriented. You’re more likely to use horizontal sorting with a grid, which doesn’t function as a table anyway.
To sort by column, follow these steps:
Select the range you want to sort—excluding any row headings that shouldn’t be sorted.
Click the Sort button in the Sort & Filter group on the Data tab.
Click the Options button in the Sort dialog box, and select the Sort Left To Right check box, as shown in Figure 22-8.
Click OK to return to the main part of the Sort dialog box.
Select the row you want to sort by and the direction of the sort. Add rows to sort by if you need them.
Click OK.
Figure 22-9 shows the result of this left-to-right sort.
Excel doesn’t recognize row headings in column-oriented sorts, so it’s best to select the range you want to sort, rather than just a single cell, when you’re sorting laterally. If you select only one cell, Excel proposes to sort all the contiguous cells, including the labels in your first column.
You need to exercise caution when sorting cells that contain formulas with cell references. If you sort by row, references to other cells in the same row will be correct after the sort, but references to cells in other rows of the list will no longer be correct.
Similarly, if you sort by column, references to other cells in the same columns will be correct after the sort, but references to cells in other columns will be broken. With either kind of sort, relative references to cells outside the list will be broken by the sort. Relative references from cells outside the sort range will keep referring to the same cells as before—even if the contents of the cell are moved by the sort.
Figure 22-10 demonstrates the hazards of sorting a range that contains formulas. Row 5 of the worksheet calculates the year-to-year change in profit using relative-reference formulas. Cell C5, for example, uses the formula =C4–B4 to calculate the difference between the 2007 profits and the 2006 profits. Each of the other formulas also references the cell directly to its left.
If you include row 5 in the sort range, the formulas get sorted along with the other columns. Each formula in row 5 still references the cell to the left, but B5 now shows an error because B4 tries to subtract the text Profit from the number 61,000.
If, on the other hand, you exclude row 5 from the sort range, the formulas keep working, but the meaning of the calculation is different. When the columns are sorted by row 1 in ascending order, the formulas in row 5 give the change from the preceding year. After you sort the columns by row 1 in descending order, the formulas in row 5 give the change from the following year. Both before and after the sort, the formulas give the change from the previous column.
Sorting is different from cutting and pasting cells. If you pick up each column and move it to its new location, Excel updates the formulas appropriately after each move. If you do it by clicking Sort on the Data tab, Excel doesn’t adjust the references.
Figure 22-10. Sorting the columns of the worksheet from the order shown above to the order shown below breaks the formulas in row 5.
To avoid the problems associated with sorting ranges containing formulas, observe the following rules:
In formulas that reference cells outside the sort range, use only absolute references.
When sorting by row, avoid formulas that reference cells in other rows. If you must use such formulas, reference cells by name, not by address.
When sorting by column, avoid formulas that reference cells in other columns. If you must use such formulas, reference cells by name, not by address.
Cells outside the sort range can make relative references to cells inside the sort range, but they always reference the cell location, not the sorted contents. To exclude the cells from the sort range, insert a blank row or column to separate them from the sorted range.
Excel ordinarily sorts text in alphabetical order, but it can sort on the basis of any of its custom lists if you want it to do so. The program includes four custom lists by default (Sun, Mon, Tues,…; Sunday, Monday, Tuesday,…; Jan, Feb, Mar,…; and January, February, March,….). If you have a column consisting of these day or month labels, you can sort them in their proper chronological order. If you’ve created other custom lists, you can sort text fields in the order of those lists as well.
Note
For information about creating and using custom lists, see Creating Custom Lists on page 237.
To sort on the basis of a custom list, simply select Custom List in the Order list in the Sort dialog box. The four default custom lists appear there, along with any others you have created.
You can use a custom list for any sort field you want. You could sort a column of month names using one custom list and a separate column of day names using a separate custom list, all within one sort operation.
Usually when Excel sorts text, it disregards case variants entirely. In other words, the program regards the letter A as equivalent to the letter a. You can change this behavior by clicking Options in the Sort dialog box and then selecting the Case Sensitive check box.
If you’re familiar with the standard character-encoding systems used by Windows (ANSI or Unicode), you might suppose that selecting the Case Sensitive check box would cause Excel to sort all capital letters before all lowercase letters. That, after all, is how those character-encoding systems are constructed. (The capital alphabet occupies the range 65 through 90 (decimal notation), and the lowercase alphabet resides at 97 through 122.) However, selecting the Case Sensitive check box does not cause Excel to perform a “straight” ANSI or Unicode sort. Instead, it makes the program put lowercase variants ahead of capital variants of the same letter.
For example, suppose the range A1:A4 holds the following four text values:
Pine
pine
tree
Tree
If you perform a default (not case-sensitive) ascending sort on these four cells, their order remains unchanged because p comes before t and Excel disregards the variation in case. If you sort again after selecting the Case Sensitive check box, the order becomes
pine
Pine
tree
Tree
because p now comes before P and t comes before T. In a conventional ANSI sort, you’d get
Pine
Tree
pine
tree
because all capitals come before all lowercase letters.