Insert subtotal rows into sorted data

Learn how to insert subtotal rows into sorted data without having to spend time doing it manually.

Recently I ran a training session for an Accounts Manager and her staff.

They spent a lot of time pulling data out of their in-house computer system, sorting it by customer and then inserting a new row at every change in the customer name.

This was so that they could place a SUM into the row to total what the customer had spent with them.

Using the Subtotals feature I showed them a quick and effective way in which to summarise their data. And yep! They were pretty impressed!

Insert Subtotals into your data with a couple of clicks

Before you insert automatic subtotals, sort your list so that the data to be sub-totaled is grouped together.

Inserting automatic subtotals

1. First make sure your data is compatible for subtotaling:

• Turn Filtering off
• Ensure that each column has a title row.
• Check that there are no blank columns or rows.

2. Sort the column that contains the data you want to base your subtotals on. For example, if you wanted to insert a subtotal row after every change in the Product Type column you would sort the data first to ensure all products are grouped together.

3. Select the range to be subtotaled and then select Subtotal from the Outline group on the Data tab.

4. From the At each change in box, select the field that you want Excel to Subtotal by when it identifies a change in the data within that field.

5. Select a function from the Use function box to calculate your Subtotals with. The Sum function is the default option for fields containing numbers and the Count function is the default for fields containing text.

6. From the Add subtotal to list, select each field that you want Excel to Subtotal.

7. Click OK. Excel will now calculate the subtotals, insert a sub-total row at each change in the selected fields and insert a Grand Total at the bottom of the list.

A sub-totaled list is displayed with Outlining symbols attached so that you can show/hide different levels of data according to your requirements. Click the outlining level number or collapse and expand buttons to control how you view the data.

Removing Subtotals

1. Select any single cell in your list.

2. From the Data tab select Subtotal.

3. From the Subtotals dialog box select Remove All. Excel will display your list in its original form.

Multiple Subtotals

Multiple subtotals allows you to display several subtotals within the one data table.

To create another subtotal:

1. From the Data tab select Subtotal.

2. In the At each change in box select the new field to subtotal by.

3. Clear the selection from the Replace current subtotals option.

4. Click OK.

Excel adds another outlining level to the list and inserts subtotals within your already sub-totaled list. Nested subtotals are removed in the same manner as normal subtotals.