Microsoft Office Tutorials and References

In Depth Information

**Chapter 25: Excel Subtotals**

CHAPTER
25

Excel Subtotals

The Subtotal feature in Excel is an additional way to summarize data. The Sub-

total

—

which does not necessarily apply to numerical sums only

—

can summarize

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

detail data.

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

’

to

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

gender.

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.

n

In the resulting menu, choose Gender for the At each change in. This is the

parameter the original data was sorted by.

n

The function you are interested in is the Average.

n