Datasheet

Book VI
Chapter 1
Building and
Maintaining Data Lists
589
Sorting Data
You can’t sort data you’ve formally formatted as a data table in this manner
until you convert the table back into a normal cell range because the pro-
gram won’t recognize the row containing the column’s new order numbers
as part of the table on which you can perform a sort. In this example, to get
around the problem, you take the following steps:
1. Click a cell in the data table and then click the Convert to Range com-
mand button on the Design tab of the Table Tools contextual tab or
press Alt+CTG.
Excel displays an alert dialog box asking you if you want to convert the
table to a range.
2. Click the Yes button in the alert dialog box to do the conversion.
3. Select all the records in the Personnel data list along with the top row
containing the numbers on which to sort the columns of the list as the
cell selection.
In this case, you select the cell range A1:H20 as the cell selection.
4. Click the Sort command button on the Data tab (or press Alt+ASS).
Excel opens the Sort dialog box. You can also open the Sort dialog box
by clicking Custom Sort on the Sort & Filter button’s drop-down list or
by pressing Alt+HSU.
5. Click the Options button in the Sort dialog box.
Excel opens the Sort Options dialog box.
6. Click the Sort Left to Right option button and then click OK.
7. Click Row 1 in the Row drop-down list in the Sort dialog box.
The Sort On drop-down list box should read Values and the Order drop-
down list box Smallest to Largest.
8. Click OK to sort the data list using the values in the top row of the cur-
rent cell selection.
Excel sorts the columns of the Personnel data list according to the
numerical order of the entries in the top row (which are now in a 1-to-8
order). Now, you can get rid of the top row with these numbers.
9. Select the cell range A1:H1 and then click the Delete button on the
Home tab.
Excel deletes the row of numbers and pulls up the Personnel data list
so that its row of field names is now in row 1 of the worksheet. Now, all
that’s left to do is to reformat the Personnel data list as a table again so
that Excel adds AutoFilter buttons to its field names and the program
dynamically keeps track of the data list’s cell range as it expands and
contracts.
30_489598-bk06ch01.indd 58930_489598-bk06ch01.indd 589 3/27/10 12:46 PM3/27/10 12:46 PM