Sorting Data

Problem

You have unsorted data in your spreadsheet and you'd like it sorted.

Solution

Select the data you want to sort, then choose Data images/U2192.jpg border=0> Sort ... from the main menu bar to open the Sort dialog box. The Sort dialog box will allow you to specify how to sort the data, as discussed below.

Discussion

Figure 3-16 shows a sample spreadsheet containing hypothetical student grades. The student names are not in alphabetical order, so I'll use Excel's sort feature to sort them from A to Z. Moreover, when I sort the names, the grades associated with each student will remain so; that is, we're not just going to sort the first column, but will also keep grade associations intact for each student name.

Figure 3-16. Unsorted grade report

To sort the data keeping everything intact, you must select the entire data range. Click the first name in the list and drag the selection to the lower-right of the range. Now select Data images/U2192.jpg border=0> Sort ... from the main menu bar. You should see the Sort dialog shown in Figure 3-17.

In this example, let's sort by student names in ascending order. Select Student Name in the "Sort by" drop-down listbox, and select Ascending next to it. In the event two names are identical, let's use the associated final exam grade as the tiebreaker, with the name of the student who has the lower final exam grade appearing first. To do this, select Final Exam from the "Then by" drop-down listbox and select Ascending next to it.

You might be wondering how Excel knows the names of the columns to include in the "Sort by" and "Then by" listboxes. Excel detects whether or not you have text in the row above the selected data. If so, it sets "My data range has" to "Header row" and uses the header row labels in the drop-down listboxes. If you don't have a header row, Excel will use the column labels in the drop-down listboxes instead.

Figure 3-17. Sort dialog box

Pressing the OK button at this point causes Excel to perform the sort. Results for this example are shown in Figure 3-18.

As you can see, the names are now in alphabetical order and their associated grades were kept intact. You need not sort on the first column of data as we did in this example. You could have just as easily selected the average grade column to sort by in order to see grades reported in ascending or descending order of each student's final score. These capabilities give you a fair amount of flexibility when sorting data for further processing or plotting using Excel's charting features.

See Also

O'Reilly's Excel Hacks book, written by David and Raina Hawley, contains a couple of sorting hacks you might find useful. Hack #29 shows you how to sort using more than three columns, while Hack #30 shows you how to perform random sorts.

Figure 3-18. Sorted grades