Home > Microsoft Office > Excel > Subtotals - Excel

Subtotals - Excel
Excel's Subtotal feature allows you to group and then display calculations for each group. Functions such as SUM, COUNT and AVERAGE calculates those values with little effort. Excel does this with an outline type grid that you can customize to display details, subtotals and grand total for each group.
This process also works in Excel 2013 & 2010.

Quick links to sections in this article: 
Create Subtotal       Expand & Collapse Subtotals

Note: Before creating a subtotal, you will have to sort the data by which you want to group. In the example below, the data is grouped according to Month.

Create Subtotal
Step 1Select a single cell containing data in the column by which you will sort all the rows. In this example, you will be sorting by State, so a cell within the State column must be selected.
Step 2:Click the Data tab. Choose Subtotal from the Outline group.
Step 3:Under At each change in, choose the heading of the column (State in this example) from the drop down list.

Step 4:
Under Use function, choose a subtotal function (Average in this example) from the drop down list.

Step 5:
Under Add subtotal to, put a check next to the the item(s) that you wish to subtotal. Any other columns containing values may be subtotaled at the same time. Just check the column name. The same function you selected (AVERAGE in this example) will be applied to all checked columns. Click OK.
Step 6:The subtotals will appear at the bottom of each group (average sales per state in this example).

Remove Subtotal
Step 1:In the list range, select a single cell containing data.

Step 2:
Click the Data tab. Choose Subtotal from the Outline group.
Step 3:
Click Remove All in Subtotal dialog box

Collapse or Expand Subtotal Details, Subtotals

Step 1:After calculating the subtotals, you can collapse the rows to show only the subtotals by clicking the "2" at the top of the subtotal pane.
  To show only the Grand Total, click the "1".
  To show Details, Subtotals and Grand Total, click the "3".

Step 2:
To collapse details for specific groups, click the "-" next to each subtotal row.
  Step 3: To expand specific collapsed details, click the "+" in front of the group.

 Copy the Subtotaled Group You must do more than a straight copy and paste when copying subtotals. Step 1:Select the range to copy.
 Step 2:Press the [F5] key to open the Go To dialog box. Click Special... in the bottom left corner.

Step 4:
Select Visible cells only and click OK.

Step 5:
Press [Ctrl] + [C] to copy the cells.

Step 6:
Select the location where you would like to paste the range (can be same or different worksheet).

Step 7:
Press [Ctrl] + [V] to paste the cells. Only the subtotaled rows will appear.

Related Articles