Insert subtotals

Example of automatic subtotals You can automatically calculate subtotals and grand totals in a list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.) for a column by using the Subtotals command on the Data menu.

If the workbook is set to automatically calculate formulas, the Subtotals command recalculates subtotal and grand total values automatically as you edit the detail data. The Subtotals command also outlines (outline: Worksheet data in which rows or columns of detail data are grouped so that you can create summary reports. The outline can summarize either an entire worksheet or a selected portion of it.) the list so that you can display and hide the detail rows for each subtotal.

 Note    If you filter data that contains subtotals, your subtotals may appear hidden. To display them again, clear all filters For more information on applying filters, see Filter a range.

  1. Make sure that each column has a label in the first row, contains similar facts in each column, and that the range has no blank rows or columns.
  2. Select a cell in the range.
  3. Do one of the following:

ShowInsert one level of subtotals

You can insert one level of subtotals for a group of data as shown in the following example.

Example of automatic subtotals

Callout 1 At each change in the Sport column…
Callout 2 …subtotal the Sales column.
  1. Sort the column that forms the group.

ShowHow?

  1. On the Data menu, click Sort.
  2. In the Sort by box, click the column.

In the example above, the range is sorted by Sport.

  1. Select any other sort options that you want, and then click OK.

For more information on sorting, see Sort a range.

  1. On the Data menu, click Subtotals.

The Subtotal dialog box is displayed.

  1. In the At each change in box, click the column to subtotal. In the example above, you would select Sport.
  2. In the Use function box, click the summary function that you want to use to calculate the subtotals. In the example above, you would select Sum.
  3. In the Add subtotal to box, select the check box for each column that contains values that you want to subtotal. In the example above, you would select Sales.
  4. If you want an automatic page break following each subtotal, select the Page break between groups check box.
  5. To specify a summary row above the details row, clear the Summary below data check box. To specify a summary row below the details row, select the Summary below data check box. In the example above, you would clear the check box.
  6. Optionally, you can use the Subtotals command again by repeating steps one through seven to add more subtotals with different summary functions. To avoid overwriting the existing subtotals, clear the Replace current subtotals check box.

ShowInsert nested levels of subtotals

You can insert subtotals for inner, nested groups within their corresponding outer groups as shown in the following example.

Example of outer and nested subtotals

Callout 1 At each change in the outer, Region column…
Callout 2 …subtotal the Sales for that region and at each change for the inner, Sport column.
  1. Sort the columns that form the groups.

ShowHow?

  1. On the Data menu, click Sort.
  2. In the Sort by and Then by boxes, click the columns, sorting first by the outer column, then by the next inner column subtotals, and so on.

In the example above, the range is sorted by Region and then by Sport, so that the detail rows for March and April within the East region are together, and the rows for each month within the West region are together.

  1. Select any other sort options that you want, and then click OK.

For more information on sorting, see Sort a range.

  1. Insert the outer subtotals.

ShowHow?

  1. On the Data menu, click Subtotals.

The Subtotal dialog box is displayed.

  1. In the At each change in box, click the column for the outer subtotals. In the example above, you would click Region.
  2. In the Use function box, click the summary function that you want to use to calculate the subtotals. In the example above, you would select Sum.
  3. In the Add subtotal to box, select the check box for each column that contains values that you want to subtotal. In the example above, you would select Sales.
  4. If you want an automatic page break following each subtotal, select the Page break between groups check box.
  5. To specify a summary row above the details row, clear the Summary below data check box. To specify a summary row below the details row, select the Summary below data check box. In the example above, you would clear the check box.
  6. Optionally, you can use the Subtotals command again by repeating steps one through six to add more subtotals with different summary functions. To avoid overwriting the existing subtotals, clear the Replace current subtotals check box.
  1. Insert the nested subtotals.

ShowHow?

  1. On the Data menu, click Subtotals.

The Subtotal dialog box is displayed.

  1. In the At each change in box, click the nested subtotal column. In the example above, you would select Sport.
  2. In the Use function box, click the summary function that you want to use to calculate the subtotals. In the example above, you would select Sum.

Select any other options that you want.

  1. Clear the Replace current subtotals check box.
  1. Repeat the previous step for more nested subtotals, working from the outermost subtotals in.

Tip    To display a summary of just the subtotals and grand totals, click the outline symbols one two three next to the row numbers. Use the plus and minus symbols to display or hide the detail rows for individual subtotals.

 
 
Applies to:
Excel 2003