Page 229 - Excel Workbook for Dummies

23_798452 ch16.qxp 3/13/06 7:40 PM Page 212
212 Part IV: Managing and Securing Data

3. Select Dept in the At Each Change In drop-down list box.
4. Select Sum in the Use Function drop-down list box.
5. Select only the Salary check box in the Add Subtotals To list box and then
select OK.

Excel subtotals the salaries in Employee Data List by department, while at the
same time adding outline buttons to the rows (Figure 16-1 shows how the first
part of this data list should now appear your Employee Data List in Excel).
You can use these outline buttons to collapse and expand the subtotals in the
data list.
6. Click the number 1 button at the top of the row header to collapse the subtotals
down to the grand total of all the salaries for all departments in row 39 and then
widen column F to display its grand total.
The first level of the outlined rows shows only the grand total. Next, display the
subtotals for each of the departments in the data list.
7. Click the number 2 button at the top of the row header.
Excel now displays the rows with the salary totals for the Accounting in row 7,
Administration in row 13, Engineering in row 26, Human Resources in row 33, and
Information Services in row 38, along with the grand total of all salaries in row 39.
8. Click the number 3 button at the top of the row header to redisplay the rows
with all the records in the data list along with the salaries department subtotals
and salary grand total. Press Ctrl+8 to hide the outline buttons.
Ctrl+8 is a shortcut key combination that you can use to hide and then redisplay
the buttons and other controls in any spreadsheet you’ve outlined.
9. Save the Employee Data List with the subtotals in a new workbook file named
Solved16-6.xls in your Chapter 16 folder in the My Practice Spreadsheets folder
and then close the workbook file.

Figure 16-1:
The first
part of the
Employee
Data List
after subto-
taling its
salaries by
department.