Excel: Add Subtotals to a Data set

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.

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

   

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

   

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

   

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