Microsoft Excel 2003/2004

Calculating Subtotals in Databases

Calculating subtotals based on subgroups of information is made easier with the Data Subtotal command. Once the data is sorted, Excel can add subtotals based on data within the database, automatically inserting a new row and adding an appropriate total heading for each line.

Adding Subtotals
Adjusting Views with Subtotals
Removing Subtotals
Re-sorting the Data

Adding Subtotals

Sort the data
NOTES:
For more information, refer to Using the Sort Command.
Sorting the database is important for grouping the information you want subtotaled.
If you do not have column labels on what you are subtotaling, Excel will prompt you to enter column labels or to choose the top row as your column labels. Be aware that if you choose the first row as your column labels, that data will not be included in the subtotals.
Select a cell within the database
From the Data menu, select Subtotals...
The Subtotal dialog box appears.
From the At each change in pull-down list, select the desired option
From the Use function pull-down list, select the desired function
From the Add subtotal to scroll box, select the desired column label
OPTIONAL: At the bottom of the dialog box, select/deselect the appropriate options
Click OK
Subtotal lines are added to the database along with outlines for adjusting the view of the data.

Adjusting Views with Subtotals

When you have applied subtotals to your database, an outline-type bar appears to the left of the row IDs.

The

represents the levels of the view.

Level 1:	grand total
Level 2:	subtotals and grand total
Level 3:	all data in the database

To expand the view one level:

Click the plus sign

To collapse the view one level:

Click the minus sign

Removing Subtotals

If you no longer need the subtotals, you can easily remove them with a command rather than deleting each row. To remove the subtotals, use the following instructions:

Select a cell within the database
From the Data menu, select Subtotals...
The Subtotal dialog box appears.
Click REMOVE ALL
The subtotals are removed.

Re-sorting the Data

If you re-sort the database, Excel will warn you that it will remove the subtotals and re-sort. After the database has been sorted, you can add subtotals again.