• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint

Sorting Lists

This chapter wouldn't have insisted that you be so fastidious about structuring lists if there weren't plenty of good reasons. Many of those reasons have to do with data management, but they don't stop there.

The list first shown in Figure 3.1 is repeated in Figure 3.8. Suppose that you wanted to sort the list, first by Sex, and then by Party within Sex, and finally by Age within Party within Sex. You would click any cell inside the list—as shown in Figure 3.8, that might be C8 or B18—and choose Sort from the Data menu.

Figure 3.8. You can choose No Header Row if you're not sorting a true list: one without variable names, perhaps.

In the first row of the range of contiguous cells, if Excel can find values that could be variable names, it treats those names as a header row. As Figure 3.8 shows, Excel excludes the variable names from the range of cells to be sorted. It lets you know by not highlighting the first row and by choosing (on your behalf) the Header row option button on the Sort dialog box.

This makes sorting very convenient. You use the dropdowns in the Sort dialog box to select the variables you want to use as the first, second, and third sort keys. If, as in Figure 3.8, you choose to sort by Age within Party within Sex, your choices appear in the dropdowns.

Suppose that you choose to sort in ascending order for all three variables: first by Sex, and then by Party, and then by Age. This pattern would sort all Females into one group of adjacent rows. Within the group of Females, it would sort the Female Democrats together and, in a different group of rows, the Female Republicans. Finally, within the Female Democrats, the pattern would sort the records in ascending order by Age.

When you have a range of cells that's so wide that its columns disappear off your screen, using a header row becomes especially convenient. Suppose that your range runs from column A to column J, and that you can see only columns A through E on your screen. You want to sort on, say, Product (column B), Sales Office (column G), and Revenue (column I). Without a header row that has variable names, you need to remember which columns are occupied by Sales Office and by Revenue—because you can't see them, you need to remember that your sort involves columns G and I. But if you're using a header row, you can see the variable names in the Sort dropdowns, and you don't need to remember where anything is.

Here's yet another reason to set your data up as a list. Assume that the data layout is as shown in Figure 3.9.

Figure 3.9. The layout is a simple transposition of the data in Figure 3.8.

As you might know, you can choose to sort left-to-right as well as top-to-bottom. Use the following to do so:

Choose Data, Sort.

Click the Options button.

Fill the Sort Left to Right option button.

Click OK to return to the Sort menu and continue as before.

The problem is that left-to-right sorts don't support header rows. Notice in Figure 3.9 that the variable names are in the first column of each row instead of in the first row of each column. If you don't exclude that column from the range to be sorted, the headers will be sorted as though they were values.


Notice that if you specify a left-to-right sort, Excel disables the Header Row and the No Header Row option buttons.

To preserve the headers in this layout, you would have to begin by selecting the entire range of values, excluding the headers in A1:A3. If you start by selecting a single cell only, Excel insists on treating the first column's variable names as values to be sorted.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint