Working with Subtotals
Subtotals allow you to group the data in a database using a particular field and then calculate a subtotal for the field data found in each group. For example, you may have an employee database that lists the department that each employee belongs to (in a Department field) and each employee's salary (in a Salary field). You can use the Subtotal feature to group the data by department and compute a subtotal of the salaries for each department.
When you work with the Subtotal feature, you are not limited to just subtotals (which makes the name of this feature a little misleading). You can actually choose from a range of formulas. You can compute the minimum, maximum, or count the number of entries in a particular subtotal group (other formulas are also available).
To Create Subtotals
1. |
Click on the database that you wish to create a subtotal for, or select the list. |
2. |
Select the Data menu, then select Subtotals. The Subtotals dialog box will appear (see Figure 9.1). Figure 9.1. Subtotals can be placed in your databases to summarize data fields.
|
3. |
Use the Group by drop-down arrow to select the field by which you wish to group the records in the database. |
4. |
To select the field that will be “subtotaled,” click the checkbox for the field in the Calculate subtotal for box. |
5. |
To select the formula that you will use to compute the subtotal, click the formula in the Use function box. |
6. |
To create additional groups, use the 2nd and 3rd Group tabs and set the parameters for each Group tab by repeating Steps 3–5. |
7. |
To set additional options for a subtotal, click the Options tab. You can place page breaks between groups or change the sort direction from ascending to descending. |
8. |
When you have selected all the parameters for the subtotal, click OK. |
The subtotals will be created in the table and the database information will be grouped by the field you selected. Figure 9.2 shows a database table that has been grouped by department, and a subtotal has been supplied for each department's salary sum.
Figure 9.2. Subtotals are placed at the bottom of each subtotal group in the database.
Note
When you subtotal data in a database, the records grouped by a particular field can be expanded and collapsed using the group icons that have been placed in the outline area at the left of the database.
To Copy and Undo Subtotals
Since subtotal lines are placed in the database, this will make it more difficult to sort the database or use the AutoFilter feature, since the records are now broken up by the subtotals.
The best way to get your database back into its original shape is to copy the “new” subtotaled database to another sheet and then undo the subtotals placed on the original database. You can then sort and filter the original database as needed.
1. |
Select the database, including the subtotals, and click the Copy tool on the Function toolbar. |
2. |
Click the Undo tool on the Function toolbar and the subtotals will disappear. |
3. |
Move to a new sheet in the spreadsheet. Click where you would like to place the copy of the database with subtotals, then click the Paste tool. |
You now have your original database on its sheet and a copy of the database with subtotals on another sheet.