Sort the list first by Salesperson and then by Product, both in ascending order.In the Subtotal dialog box, choose Remove All.Įxercise In the following exercise, you will use automatic subtotals in a list.If necessary, select the Replace current subtotals and Summary below data check boxes, and deselect the Page break between groups check box.In the Add subtotal to list box, select the desired subtotal field check box(es).From the Use function drop-down list, select a subtotal function.In the Subtotal dialog box, from the At each change in drop‑down list, select a field.In the Outline group on the Data tab, click the Subtotal button.

If necessary, select a cell in the list.

Sort the list by the column for which you want to display subtotals.

Both grand total and subtotal labels are displayed in bold in the list and are automatically recalculated as you edit detail rows. Grand total values are calculated by using the same summary function that you use to calculate subtotal values. The grand total values are derived from the detail data, which is the list data, and not from the subtotal rows. Inserts a page break between each grouping.Įxcel calculates subtotal values by using a summary function, such as Sum or Average. Identifies the fields you wish to subtotal. Identifies the summary function used to calculate subtotals. Identifies the field that contains the items by which you want to subtotal values in other columns.

The options available in the Subtotal dialog box are summarized in Table 1-1.įor hands-on Microsoft Excel training classes in Los Angeles call us on 888.815.0604.įigure 1-19: The Subtotal Dialog Box At Each Change In You determine how you want to subtotal your data using the Subtotal dialog box, shown in Figure 1-19. For example, in a list of sales records, you sort by salesperson to obtain the subtotals for the number of product units sold by each salesperson. To use automatic subtotals, your data must be organized into a list and sorted by the column for which you want to display subtotals. Inserting automatic subtotals is a quick way to summarize data in a list. To get a better idea of each person’s performance by product, you can use the Subtotals command to get a subtotal for each product sold by each salesperson. A list of sales records may include the names of all salespersons, the different products they have sold, and the units of each product sold.

When working with a list, you often need to know the bottom line figures or totals.

How to calculate subtotals in excel