Objectives
Using Excel help (F1):
In the
Search For box, type Subtotal,
and press the Enter key.
In the
Search Results list, click on About subtotals, and read the topic.
In the
Search Results list, click on Insert subtotals, and read the topic.
In the
Search Results list, click on Remove subtotals, and read the topic.
In the
Search Results list, click on Calculating subtotals and working with
levels in Excel, and read the topic.
Excel Bible: Chapter 19
Subtotals
Sometimes after sorting data, you may want
to calculate some value for all of the data with the same key (e.g. counting
the number of students with the same major). The Data | Subtotals command is used for
this. Note that much of what can be done with the Subtotals command can also be
done with pivot tables.
The file Volcano Coffee
Company.txt is used in the next examples. Note that this is a text file and must be
"imported" using the Text Import Wizard.
Example: Finding the total sales by product
·
Sort the data using Product as a key.
·
To do this, remove any existing subtotals. Make sure that the cursor is
in your data list. From the menu, choose Data | Subtotals | Remove All.
·
From the menu, select Data | Subtotals.
·
In the "At each change in:" text box, choose Product.
·
In the "Use function:" text box, choose Sum.
·
In the "Add subtotal to:" text box, choose the column you want
to put the count in. Your results will probably be easier to read if you put
the count in the rightmost column (the Revenue column).
·
Select "Replace current subtotals".
·
De-select "Page break between groups".
·
Select "Summary below data".
·
Click on OK.
The data will be grouped by Product, with the total sales in each
group listed below it in the Revenue column.
Example: Finding the total sales by region
·
Sort the data using Region as a key.
·
To do this, remove any existing subtotals. Make sure that the cursor is
in your data list. From the menu, choose Data | Subtotals | Remove All.
·
From the menu, select Data | Subtotals.
·
In the "At each change in:" text box, choose Region.
·
In the "Use function:" text box, choose Sum.
·
In the "Add subtotal to:" text box, choose the Revenue column.
·
Select "Replace current subtotals".
·
De-select "Page break between groups".
·
Select "Summary below data".
·
Click on OK.
The data will be grouped by Region, with the total sales in each
group listed below it in the Revenue column.
Excel gives us three choices for viewing
subtotals.
·
View all detail records (the original list data) and all summary records
(the ones at the bottom of each group)
·
View only the summary records (and hide the original list data).
·
View only the overall statistic (e.g. overall average, overall count,
etc.)
Example: View only
the summary records from the Revenue subtotals.
·
Click on the "2" at the top of the left margin.
Example: View only
the overall Revenue (the single line at the bottom of the subtotals).
·
Click on the "1" at the top of the left margin.
Example: Return to
the original view and view detail records, summary records, and the overall total
revenue.
·
Click on the "3" at the top of the left margin.
Remove subtotals
·
From the menu, choose Data | Subtotals | Remove All
Re-sorting the subtotals
Although the data must be sorted before the Data |
Subtotals
command can do anything useful, once the subtotals have been calculated, you
can re-sort the data based on the subtotals.
Example: Sort by descending revenue
·
Put the cursor in the subtotals column (Revenue).
·
Click on the "Sort Descending" button on the toolbar. This will
sort the subtotals.
Example: Finding the average monthly sales
·
Sort the data using Month as a key.
·
To do this, remove any existing subtotals. Make sure that the cursor is
in your data list. From the menu, choose Data | Subtotals | Remove All.
·
From the menu, select Data | Subtotals.
·
In the "At each change in:" text box, choose Month.
·
In the "Use function:" text box, choose Average.
·
In the "Add subtotal to:" text box, choose the Revenue column.
·
Select "Replace current subtotals".
·
De-select "Page break between groups".
·
Select "Summary below data".
·
Click on OK.
The data will be grouped by Month with the average sales each
group listed below it in the Revenue column.
More examples
Sample data file: Advising.txt.
Note that this is a text file and must be
"imported" using the Text Import Wizard.
Example: Counting the number of
students in each major
·
To do this, sort the data using Major as a key.
·
From the menu, select Data | Subtotals.
·
In the "At each change in:" text box, choose Major.
·
In the "Use function:" text box, choose Count.
·
In the "Add subtotal to:" text box, choose the column you want
to put the count in. Your results will probably be easier to read if you put
the count in the rightmost column (the ID column).
·
Select "Replace current subtotals".
·
De-select "Page break between groups".
·
Select "Summary below data".
·
Click on OK.
The data will be grouped by major, with the number of records in each
group listed below it in the ID column.
Example: Determining the average
number of credits by students in each major
·
To do this, remove any existing subtotals. Make sure that the cursor is
in your data list. From the menu, choose Data | Subtotals | Remove All.
·
From the menu, select Data | Subtotals.
·
In the "At each change in:" text box, choose Major.
·
In the "Use function:" text box, choose Average.
·
In the "Add subtotal to:" text box, choose the column you want
the average number of credits to appear in (the Credits column seems like an
obvious choice).
·
Select "Replace current subtotals".
·
De-select "Page break between groups".
·
Select "Summary below data".
·
Click on OK.
The data will be grouped by major, with the average number of credits for
each student in the major listed below each group in the Credits column.
Example: Sort the majors in
descending order based on the number of students in each major.
Use the Advising.xls file for this. Put the
cursor in the data list.
·
From the menu, choose Data | Subtotals | Remove All
·
From the menu, choose Data | Subtotals.
·
In the "At each change in" text box, choose Major.
·
In the "Use the function" text box, choose Count.
·
In the "Add subtotal to" text box, you can choose any field (it
doesn't matter which field we count, we will always get the same number).
·
De-select "Page break between groups".
·
Select "Summary below data".
·
Click on OK. The data will be grouped by major, with the number
of records in each group given at the bottom of each group.
·
Click on the "2" at the top of the left margin.
·
Put the cursor in the subtotals column.
·
Click on the "Sort Descending" button on the toolbar. This will
sort the subtotals.
Updated 2005.10.10