Microsoft Excel 2008
Calculating Subtotals
Once you have sorted a data range into groups, Excel's Subtotal command can create a subtotal for each group, automatically inserting a new row and displaying the total for each line.
Adding Subtotals
Subtotals are added using the Subtotal dialog box, which is accessed from the Data menu.
NOTES:
The Subtotal command is not available for Excel Databases. To find subtotals for Database data, you can convert it to a normal data range and then perform the Subtotal command.
The following graphics show a data range that has been sorted according to days of the week, and its corresponding Subtotal dialog box, which has been set to display subtotals for the total hours worked on each day of the week.
-
Select a cell within the data range
-
From the Data menu, select Subtotals...
The Subtotal dialog box appears.
-
From the At each change in pull-down menu, select the column containing data groups for which you want subtotals
NOTE: It is important to have this column sorted into groups, because the Subtotal command subtotals numerical data at every change of group in the column.
EXAMPLE: Select Day -
From the Use function pull-down menu, select the function that will be used to create subtotals
EXAMPLE: Select Sum
Sum
adds the values for each data group
Count
determines the number of records in each data group
Average
finds the average value in each data group
Max
finds the highest value in each data group
Min
finds the lowest value in each data group
Product
multiplies compounding values in each data group
-
From the Add subtotal to scroll box, select the column with the numerical data you want subtotaled
NOTE: The option is selected when a checkmark appears. -
OPTIONAL: From the bottom of the dialog box, select the appropriate options
-
Click OK
Subtotal lines are added according to the column you specified in the At each change in pull-down menu and the groups you sorted in that column.
Adjusting Views with Subtotals
When you have applied subtotals to your data range, an outline bar appears to the left of the row numbers.
The
presents levels of detail in the view
Level 1: grand total
Level 2: subtotals and grand total
Level 3: all data in range
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 subtotals, you can easily remove them without deleting rows.
-
Select a cell within the data range
-
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 a data range containing subtotals, Excel will warn you that it will remove the subtotals before re-sorting. After the data range has been re-sorted, you can add subtotals again.
