Add Subtotals to a Data set

September 19, 2022 - by Bill Jelen

Problem: I have a lengthy report with invoice detail by customer. I need to add a subtotal at each change in customer.

Strategy: You can use the Subtotal feature to solve this problem in seconds instead of minutes.

1. Sort the data by customer.
2. Select a single cell in the data set. Then select Data, Subtotal. As shown below, the Subtotal dialog assumes that you want to subtotal by the field in the leftmost column of your data. It also assumes that you want to total the rightmost field.

Sort the data by Customer Choose Data, Subtotals. By default the Subtotal dialog box says as Each Change In (the first column), Use the Sum function, on (the last column). Of the three checkboxes at the bottom, Replace Current Subtotals and Summary Below Data are checked. The Page Break Between Groups is unchecked. Three buttons at the bottom are Remove All, OK, and Cancel. — Figure 699. The defaults in the Subtotal dialog are usually wrong.

3. Open the At Each Change In dropdown and choose Customer.
4. The Use Function dropdown in this case is already Sum. If your data set has a text column as the right-most column, this will say Count. Change it back to Sum.
5. Checkmark any numeric fields that should have a subtotal. In this case, Quantity, Revenue, and COGS. Profit is already checked because it is the right-most field.
6. If you want every customer on their own page, use Page Break Between Groups, although I am not selecting that option in this case.

Make these changes in the Subtotal dialog box: At each change in Customer. In the Add Subtotal To, select Quantity, Revenue, COGS in addition to Profit. — Figure 700. Subtotal at each change in customer.

7. Click OK. Excel will insert subtotals at each change in customer.

Part of the results after adding subtotals. Row 135 is inserted with a CitiGroup Total. Row 140 is inserted with a Compaq Total. Row 145 is inserted with Duke Energy Total. To the left of the grid, three new Group and Outline buttons with 1, 2, and 3. — Figure 701. In seconds, Excel will insert new rows with subtotals.

If you scroll to the end of the data set, you will notice that Excel added a grand total of all customers. The inserted rows use the relatively new SUBTOTAL function. This function will total all the cells in the range except for cells that contain other SUBTOTAL functions.

The Grand Total row inserted by the Subtotals command uses a formula of =SUBTOTAL(9,F2:F590). This ignores the other subtotals in the range. — Figure 702. Excel adds a grand total at the very bottom.

Additional Details: In order to remove subtotals, you select a cell in the data set and then select Data, Subtotal. In the Subtotal dialog, you click the Remove All button.

Gotcha: This example works because the data was sorted by customer. If the data were sorted by invoice number instead, the result would be fairly meaningless.

This article is an excerpt from Power Excel With MrExcel

Title photo by Valdemaras D. on Unsplash