1. SUBTOTAL Function
The SUBTOTAL function calculates a column of numbers
based on the code used in the function. With the correct code, SUBTOTAL
can calculate averages, counts, sums, and eight other functions listed
in Table 1. It can also ignore hidden rows when the 100 version of the code is used.
Table 1. SUBTOTAL Function Numbers
| Function_num (includes hidden values) | Function_num (ignores hidden values) | Function |
|---|
| 1 | 101 | AVERAGE |
| 2 | 102 | COUNT |
| 3 | 103 | COUNTA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUCT |
| 7 | 107 | STDEV |
| 8 | 108 | STDEVP |
| 9 | 109 | SUM |
| 10 | 110 | VAR |
| 11 | 111 | VARP |
See Figure 1
for examples of the SUBTOTAL function in action. The function will
ignore any cells in the range that include SUBTOTAL functions
themselves, as shown in column E of the worksheet in the figure. Column
G uses the SUM function instead of SUBTOTAL and does not ignore the
hidden rows or previous SUM formulas in the Grand Total.
2. Subtotal Tool
The
SUBTOTAL function is very useful, but if you have a large dataset it
can be time consuming to insert all the Total rows. When your dataset
is large, use the Subtotal tool from the Data tab in the Outline group.
This tool will group the sorted data, applying the selected function.
From the Subtotal dialog, you can select the column
to group the data by, the function to subtotal by, and which columns to
apply the subtotal to.
Summarizing Data Using the Subtotal Command
To quickly group and apply subtotals to a dataset, follow these steps:
1. | Sort the data by the column the summary should be based on. For example, if summarizing by region, sort the Region column.
|
2. | Select a cell in the dataset.
|
3. | Go to Data, Outline, Subtotal. The Subtotal dialog, shown in Figure 2, will open.
|
4. | From the At Each Change In field, select the column by which to summarize the data.
|
5. | From the Use Function field, select the function to calculate the totals by.
|
6. | From
the Add Subtotal To field, select the columns the totals should be
added to. Notice that, by default, the last column is already selected.
|
7. | Click OK. The data will be grouped and subtotaled, with a grand total at the very bottom, as shown in Figure 3.
|
Placing Subtotals Above Data
By default, subtotals appear below the data being
summarized. If the subtotals need to appear above the data instead,
deselect Summary Below Data in the Subtotal dialog.
Remove Subtotals or Groups
To remove all the subtotals and groups,
click the Remove All button in the Subtotal dialog. To remove only the
group and outline buttons, leaving the subtotal intact, select Data,
Outline, Ungroup, Clear Outline.
