The Excel Subtotal command provides a quick way of producing subtotals for individual fields in a table of data.
Note that this command is not the same as the Excel Subtotal Function, which performs mathematical operations for a range of visible cells.
We will illustrate the Excel Subtotal command using the above spreadsheet on the right, which lists the sales figures for three teams during the first three months of the year.
The following steps can be used to display subtotals for each of the months, Jan, Feb & Mar:
Click on the Subtotal option which is located in the 'Outline' group, within the Data tab of the Excel ribbon.
This will cause the Subtotal dialog box to open up (see aboveright). Within this box:
These options tell Excel to display a subtotal every time the value in the Month column changes and that this subtotal should display the Sum of the data in the Sales column.
The resulting spreadsheet is shown below:
Clicking on the outlines, to the left of the table allows you to hide or display the details of each section of your data table.
The Subtotal dialog box offers further options, in addition to those used in the above example. The options are each described below:
The At each change in: field of the Subtotal dialog box allows you to select which field of your data table you want the Subtotals to apply to.
For example, in the spreadsheet above if we had selected the field Team, a subtotal would have appeared each time the team name changed (although in this case, it would be more useful to order the data table by team before applying the subtotals).
The Use function: field of the Subtotal dialog box allows you to select a mathematical operation that is to be performed on the groups of data.
For example, in the spreadsheet above, instead of calculating the Sum of the monthly sales figures, we could have calculated the Average sales figure for each month.
The Add subtotal to: field of the Subtotal dialog box allows you to select which columns of your table you want the selected mathematical operation to be applied to. You can select multiple fields to apply the subtotal operation to, although this option really only makes sense for fields containing numbers.
The Replace current subtotals checkbox gives you the option to add more than one subtotal to a data table.
For example, if, in the above spreadsheet, we wanted to show the Sum and the Average sales figures for each month, we could first apply the Sum subtotal (as in the example) and then we could select the subtotal option again and request the Average calculation. Following this:
The above spreadsheet on the right shows the result after applying both the Sum and the Average subtotals.
If you check the Page break between groups checkbox, Excel will insert page breaks into your spreadsheet after each group of data. This may be useful if you want to print out your spreadsheet.
The Summary below data checkbox simply specifies where the group summaries are positioned.
In order to remove the subtotals from your table:
Click on the Subtotal option (within the Data tab of the Excel ribbon):
Further examples of the Excel Subtotal command are provided on the Microsoft Office Support website.