Use Subtotals

Use Subtotals

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.