Microsoft Office Tutorials and References
In Depth Information
Chapter 25: Excel Subtotals
The Subtotal feature in Excel is an additional way to summarize data. The Sub-
which does not necessarily apply to numerical sums only
and display your data in a logical way.
Excel can automatically calculate subtotal and grand total values in a table.
When you insert automatic subtotals, Excel outlines the table so that you can display
and hide the rows
details for each subtotal. This is the same concept as was explored
in the previous chapter about outlines.
Subtotals are calculated with a summary function. (Examples of summary
functions include Sum, Count, and Average.) Subtotal calculates subtotal values with
one of 11 different functions. It is possible to utilize more than one type of summary
function for each column.
To insert subtotals, you have to organize the table so that the rows you want to
subtotal are grouped together by a certain parameter or type of information. You can
calculate subtotals for any column that contains numerical values. However, only the
Count function can be applied to nonnumeric data. If you attempt to calculate any
other function for nonnumeric data, the result will be zeros for the sum and errors
for all other functions.
Grand total values are derived from the data, not from the values in the subtotal
rows. If you use the Average Summary function, the grand total displays an average
of all the rows
details in the list, not an average of the values in the subtotal rows (it
is not the average of the averages).
Excel recalculates subtotal and grand total values automatically as you edit the
The following exercise will make use of the same data used in previous chapters.
The data is shown in Figure 25.1.
Before proceeding with the Subtotal function, you need
sort the data. If you wish to get the averages for the ages of females and males, that
data needs to first be sorted by gender. See Figure 25.2, in which the data is sorted by
In the Data ribbon, use Subtotal to get the average age of females and males. The
process is shown in Figure 25.3.
as I said before
Click on the Subtotal icon in the Data ribbon.
In the resulting menu, choose Gender for the At each change in. This is the
parameter the original data was sorted by.
The function you are interested in is the Average.