The Subtotals command on the Data menu helps you to organize a list by displaying records in groups and adding subtotals, averages, or other summary information. The Subtotals command can also display a grand total at the top or bottom of your list, letting you quickly add up columns of numbers. As a bonus, Subtotals displays your list in Outline view so that you can expand or shrink each section in the list simply by clicking.
To add subtotals to a list, follow these steps:
Table 22-1. Summary Functions in the Subtotal Dialog Box
Function | Description |
---|---|
SUM | Add up the numbers in the subtotal group. |
COUNT | Count the number of nonblank cells in the group. |
AVERAGE | Calculate the average of the numbers in the group. |
MAX | Display the largest number in the group. |
MIN | Display the smallest number in the group. |
PRODUCT | Multiply together all the numbers in the group. |
COUNT NUMS | Count the number of cells containing numeric values in the group. |
STDDEV | Estimate the standard deviation based on a sample. |
STDDEVP | Calculate the standard deviation for an entire population. |
VAR | Estimate the variance in the group based on a sample. |
VARP | Calculate the variance for an entire population. |
TIP
You can choose the Subtotals command as often as necessary to modify your groupings or calculations. When you have finished using the Subtotals command, click Remove All in the Subtotal dialog box.
When you use the Subtotals command in Excel to create outlines, you can examine different parts of a list by clicking buttons in the left margin, as shown in Figure 22-7. (Note that this is similar to the way Outline view functions in Microsoft Word.)
Click the numbers at the top of the left margin to choose how many levels of data you want to see. Click the plus or minus button to expand or collapse specific subgroups of data.
Figure 22-7. The Subtotals command creates an outline view of your list.