Calculating subtotals and working with levels in Excel

Applies to
Microsoft Office Excel 2003
Microsoft Excel 2002
Picture of book cover This article was adapted from Microsoft Office Excel 2003 Step by Step by Curtis Frye. Visit Microsoft Learning to buy this book.

You can have Excel calculate subtotals or totals for portions of your worksheet data. For example, in a worksheet with sales data for three different product categories, you can first sort the products by category, and then select all the cells that contain data and open the Subtotal dialog box (Data menu, Subtotals command).

Picture of Subtotal dialog box

In the Subtotal dialog box, you can choose the column on which to base your subtotals (such as every change of value in the Week column), the summary calculation that you want to perform, and the column or columns with values to be summarized. For example (as shown in the previous picture), you could calculate subtotals for the number of units sold in each category. After you define your subtotals, they appear in your worksheet.

Picture of groups of rows that form an outline

As the previous picture shows, when you add subtotals to a worksheet, Excel also defines groups based on the rows used to calculate the subtotals. The groupings form an outline of your worksheet based on the criteria that you used to create the subtotals.

Picture of outlined subtotals

There are three types of controls in the outline section:

  • Hide detail buttons    When the rows in a group are visible, a hide detail button Hide detail button image appears next to the group.
  • Show detail buttons    When you hide a group of rows, the button next to the group changes to a show detail button Show detail button image. Clicking a show detail button restores the rows in that group to the worksheet.
  • Level buttons    Each of the numbered level buttons Numbered level button image represents a level of organization in a worksheet; clicking a level button hides all levels of detail below that of the button you clicked.

The following table identifies the three levels of organization in the previous graphic.

Level Description
1 The grand total
2 Subtotals for each group
3 Individual rows in the worksheet

In the worksheet shown in the previous picture, clicking the level 2 button would hide the rows with data on the sales of individual products, but would leave the row with the grand total (level 1) and all rows with the subtotals for each product (level 2) visible in the worksheet.

Picture of second group level

For additional flexibility, you can add levels of detail to the outline that Excel creates, which enables you to hide specific details from time to time.

Create a new outline group within an existing group

  1. Select the rows you want to group.
  2. Point to Group and Outline on the Data menu, and then click Group.

Excel will create a new group on a new level (level 4), as shown in the following picture.

Picture of multiple group levels

Remove a group

  1. Select the rows in a group.
  2. Point to Group and Outline on the Data menu, and then click Ungroup.

 Tip   If you want to remove all subtotals from a worksheet, click Subtotal on the Data menu, and then click Remove All.

Applies to:
Excel 2003