Page 225 - Excel Workbook for Dummies

23_798452 ch16.qxp 3/13/06 7:40 PM Page 208
208 Part IV: Managing and Securing Data
Excel then applies the selected sort (ascending or descending) to the data in the key
field or row to determine how the records or fields will be reordered during sorting.

When a key field contains duplicates entries, Excel simply lists these records in the
order in which they were entered in the data list. To indicate how Excel should order
records with duplicates in the primary key, you define a secondary key. For example,
if, when organizing the data list in alphabetical order by the Last Name field, you have
several records where the last name is Smith, you can have Excel order the Smiths’
records in alphabetical order by first name by defining the First Name field as the sec-
ondary key. If the secondary key contains duplicates (say you have two John Smiths
in your company), you can define a third key field (the Middle Name field, if your data
list has one) that determines how the duplicate John Smith records are arranged
when the data list is sorted.

Keep in mind that although sorting is most often applied to rearranging and maintain-
ing data list records and fields, you can use the Data➪Sort command to reorder data
in any worksheet table, whether or not the table follows the strict data list structure.

Try It

Exercise 16-3: Sorting the Records in a Data List

Use the Employee Data List in the Solved16-2.xls file that you edited in the previous
exercise to practice sorting records in a data list:

1. Choose Data➪Sort to open the Sort dialog box.
The Sort dialog box appears and Excel selects all of the records in your data list
(omitting the field names in the first row because the Header Row option button
in the My Data Range Has section is selected).
The Sort dialog box contains three text boxes, the first of which indicates the
field (column) to use as the primary sorting key, the second of which indicates
the field to use as the secondary sorting key, and the third of which indicates the
field to use as the tertiary sorting key.
Begin by sorting the records in the data list so that they are in alphabetical order
by department and then by descending order by salary.
2. Select the Dept field in the Sort By drop-down list box and leave its Ascending
option button selected. Next select the Salary field in the first Then By drop-
down list box and select its Descending option button before you select OK.
Excel reorders the records in the Employee Data List so that first they are in
alphabetical order by department (Accounting, Administration, Engineering, and
so on) and then within each department in order from highest to lowest salary
($38,000, $34,400, $29,000 and so on).
Next sort the records in the data list alphabetically by department, location, and
then last name. To perform this sort operation, you need to define three keys all
using the Ascending option.
3. Open the Sort dialog box and then define the Dept field as the Sort By key, the
Location field as the first Then By key, and the Last Name field as the second Then
By key, each with its Ascending option button selected, before you select OK.
Excel reorders the records in the Employee Data List so that that first they are in
alphabetical order by department (Accounting, Administration, Engineering, and
so on), and then within each department in order by location (Boston, Detroit,
San Francisco, and so on), and finally in order by Last Name (Edwards, Percival,
Savage, and so on).
4. Sort the records in the Employee Data List in order from the most recent date of
hire to the least recent.