The Excel Subtotal Command

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.

Subtotal Example

Spreadsheet of Sales Figures Over Three Months

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:

Select any cell within your data table (Excel will automatically detect the entire data range for use in the subtotal command);
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:
- Within the At each change in: input field, select Month;
- Within the Use function: input field, select Sum;
- Within the Add subtotal to: field, ensure that the Sales option is checked;
- Click OK.

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:

Spreadsheet of Sales Figures with Subtotals Applied

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.

Other Subtotal Options

The Subtotal dialog box offers further options, in addition to those used in the above example. The options are each described below:

At each change in:

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).

Use function:

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.

Add subtotal to:

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.

Replace current subtotals

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:

If the Replace current subtotals box is checked, the second subtotal request (for the Average) will replace the Sum subtotals;
If the Replace current subtotals box is not checked, the second subtotal request (for the Average) will be displayed in addition to the Sum subtotals.

Spreadsheet of Sales Figures with Sum and Average Subtotals Applied

The above spreadsheet on the right shows the result after applying both the Sum and the Average subtotals.

Page break between groups

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.

Summary below data

The Summary below data checkbox simply specifies where the group summaries are positioned.

If the Summary below data box is checked, the subtotals are positioned below each data group;
If the Summary below data box is not checked, the subtotals are positioned at the top of each data group.

How to remove Subtotals From Your Spreadsheet

In order to remove the subtotals from your table:

Select any cell within the data table;
Click on the Subtotal option (within the Data tab of the Excel ribbon):
When the Subtotal dialog box opens up, click on the Remove All button.

Further examples of the Excel Subtotal command are provided on the Microsoft Office Support website.

Return to the Basic Excel Page
Return to the ExcelFunctions.net Home Page