Page 228 - Excel Workbook for Dummies

23_798452 ch16.qxp 3/13/06 7:40 PM Page 211
Chapter 16: Building and Maintaining Data Lists 211
The fields of the Personnel Data List are now arranged in this order: SSN,
Department, Supervisor, First Name, Middle Name, Last Name, Title, and Salary.
7. Delete the top row of the Personnel Data List (Edit➪Delete and select Entire Row
option button).
8. Widen column B with the Department field so that all of its entries are displayed
in the worksheet.
9. Save the Personnel Data List with the sorted fields and records in a new
workbook named Solved16-5.xls in your Chapter 16 folder in the My Practice
Spreadsheets folder and then close the workbook file.

Subtotaling a List

You can use Excel’s Subtotals feature to subtotal data in a sorted data list. To subtotal
a data list, you first sort the records in the list on the field you want subtotaled and
then designate the field that contains the values you want totaled. (These don’t nec-
essarily have to be the same fields in the data list.)

When you use the Subtotals feature, you aren’t restricted to having the values in the
designated field added together with the SUM function. You can instead have Excel
return the number of entries with the COUNT function, the average of the entries
with the AVERAGE function, the highest entry with the MAXIMUM function, the
lowest entry with the MINIMUM function, or even the product of the entries with
the PRODUCT function.

Try It

Exercise 16-6: Subtotaling the Records in a Data List
Open the Exercise16-6.xls file in your Chapter 16 folder in the My Practice Spreadsheets
folder on your hard disk or in the Excel Workbook folder on the workbook CD-ROM.
This workbook contains a copy of the Employee Data List that you can use to practice
using Excel’s Subtotal feature:
1. Sort the Employee Data List first alphabetically by department and then numeri-
cally, from highest-to-lowest order by salary.
Be sure to select the Descending option button after selecting Salary as the first
Then By key.
You sort the records in the data list by the Dept field and then the Salary field
because you want Excel to subtotal the salaries in each department. The Dept
field provides what is referred to as the break in the subtotals report because
each time the department entry changes, Excel computes a subtotal (in this
case, a subtotal of the values entered in the Salary field).
2. Choose Data➪Subtotals to select the data in the Employee Data List while at the
same time opening the Subtotal dialog box.
You must supply at least three pieces of information in the Subtotal dialog box:
• Field at which the subtotals break in the At Each Change In drop-down list box
• Function to use in computing the subtotals (SUM is the default, but you can
also select COUNT, AVERAGE, MAX, MIN, or PRODUCT) in the Use Function
drop-down list box
• Field(s) whose values are to be subtotaled by selecting the check box in
front of the field name in the Add Subtotals To list box