Sorting Tables and Other Ranges

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 2007 enhances sorting even more by making it easy to sort by as many fields as you want, not just the three-at-a-time limit of previous versions. And, you can now 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.

Sorting on a Single Column

To sort on a single column-the Last Name column in Figure 21-4, for example-select one cell anywhere within that column. Then either click 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 click 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 21-4: One easy way to sort on a single column is to right-click a cell in the column and choose Sort.

On the CD You'll find the  Staff.xlsx file in the Sample Files section of the companion CD.

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 is headers (and should not be sorted) or data (and should be sorted). If you're sorting a list that is not a table, Excel guesses whether the first row is 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 21-5, appears. If you've sorted the range before, the dialog box will display the sort parameters you last used.

Figure 21-5: The Sort dialog box remembers the last sort settings you used..

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.

Sorting on More Than One Column

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 21-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 21-6. Excel then rearranges the list as shown in Figure 21-7.

Figure 21-6: To sort on three columns, you supply the names of the column headings in the Sort By and Then By boxes.

Figure 21-7: The rows are now arranged in descending order by salary, with rows of common salary arranged first by sex and then by last name.

Sorting Only Part of a List

If you sort a table, Excel always sorts the entire table, regardless of how many cells within the table you initially select. If you want to sort part of a list, make sure it's not a table.

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 21-4, for example, you start by selecting A10:G20. If you select one column from something that appears to be a list, Excel will ask 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. Explore your worksheet immediately after a sort, and use the Undo command if you don't like what you get.

Sorting by Column

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 the 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:

1. Select the range you want to sort-excluding any row headings that shouldn't be sorted.

2. Click the Sort button in the Sort & Filter group on the Data tab.

3. Click the Options button in the Sort dialog box, and select the Sort Left To Right option, as shown in Figure 21-8.

4. Click OK to return to the main part of the Sort dialog box.

5. Select the row you want to sort by and the direction of the sort. Add rows to sort by if you need them.

6. Click OK.

Figure 21-8: Use the Sort Left To Right option to reorder the years into a descending sequence.

Figure 21-9 shows the result of this left-to-right sort.

Figure 21-9: The lateral sort specified in Figure 21-8 generates this rearrangement of the data.

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 will propose to sort all the contiguous cells, including the labels in your first column.

Sorting Cells That Contain Formulas

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 got moved by the sort.

Figure 21-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 2006 profits and the 2005 profits. Each of the other formulas also references the cell directly to its left.

Figure 21-10: Sorting this worksheet laterally has broken the formulas in row 5.

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 were sorted in ascending order, the formulas in row 5 gave the change from the preceding year. After you sort the columns 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.

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.

Understanding the Default Sorting Sequence in Excel

To avoid surprises, you should understand the following points about the way Excel sorts:

• Excel sorts cells according to their underlying values, not their current number formats. This means, for example, that Excel places a date cell formatted as November 16, 2007, ahead of a date cell formatted as 12/27/2007 (because the first date has a lower numeric value), even though if you typed the two dates as text, Excel would reverse their order.

• Excel sorts numeric values ahead of text values. The value 98052 would therefore be sorted ahead of the value 1 Microsoft Way, because the former is a number and the latter is text.

• Logical values are sorted after text, and FALSE is sorted before TRUE.

• Error values (#DIV/0!, #NAME?, #VALUE, #REF!, #N/A, #NUM!, and #NULL!) are sorted after logical values. Excel regards all error values as equivalent; that is, it leaves them in the order it finds them.

• Blanks are placed last, in both ascending and descending sorts.

• The sort order for text depends on many factors, including your current locale settings. If knowing the exact sort order is important for you, type the formula =CHAR(ROW()) in cell A1. It will return a blank, because the first character in the ANSI character set is not printable. Copy the formula down 255 rows. Convert the formula to values and sort it. That will show you the sort order Excel is using. For reference, here's the current sort order for most Western European characters:

   ' - [space] ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ ¡ ¦ ¨ ¯ ´ ¸ ¿ ' ' ¢ £ ¤ ¥ + < = > ± « » × ÷ § © ¬ ® ° µ ¶ · 0 ¼ ½ 1 ¹ 2 ² 3 ³ ¾ 4 5 6 7 8 9 A a ª Á á À à Â â Ä ä Ã ã Å å Æ æ B b C c Ç ç D d Ð ð E e É é È è Ê ê Ë ë F f G g H h I i Í í Ì ì Î î Ï ï J j K k L l M m N n Ñ ñ O o º Ó ó Ò ò Ô ô Ö ö Õ õ Ø ø P p Q q R r S s ß T t Þ þ U u Ú ú Ù ù Û û Ü ü V v W w X x Y y Ý ý ÿ Z z 

Note that an apostrophe wins over a hyphen, which wins over a space, and all three come before other letters and numbers. This means they are essentially ignored unless they are the only difference between entries.

Sorting Months, Weekdays, or Custom Lists

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.

For information about creating and using custom lists, see "Creating Custom Lists" on page 218.

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 will 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.

Performing a Case-Sensitive Sort

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 will remain 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 because t comes before T. In a conventional ANSI sort, you'd get

Pine

Tree

pine

tree

because all capitals come before all lowercase letters.