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

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.

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.

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 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 . Clicking a show detail button restores the rows in that group to the worksheet.
• Level buttons  Each of the numbered level buttons 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.

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.

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.

   

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.