Excel Yourself: Automating subtotals

Excel Yourself

When you have lists of data, you may need to insert subtotals to help summarise the list.

When you have lists of data, you may need to insert subtotals to help summarise the list. Inserting them manually can be time-consuming. That’s where Excel comes to the rescue.

Excel can automatically insert subtotals into a list.

Figure 1

Figure 1

There are only two limitations:

1.    The list must be sorted by the column you want to subtotal

2.    You can’t add automated subtotals to a Formatted Table (a table created via the Format As Table icon on the Home Ribbon)

Figure 2

Figure 2

If the list isn’t already sorted by the column, then all you need to do is right-click any cell in the column and chose Sort. Then choose ascending or descending. See Figure 1

Figure 2 shows the sorted list ready to insert the subtotals.

Select any cell within the list and click the Data Ribbon tab and then click the Subtotal icon on the right-hand side. See Figure 3

The Subtotal Dialog is displayed. See Figure 4

Figure 3

The top drop-down lets you choose the column to subtotal on.

The second drop-down defaults to SUM - the most popular calculation.

Use the tick boxes in the Add subtotal to section to select the columns to subtotal - these are the value columns.

Figure 4

Figure 4

Click OK and the list will automatically have the subtotals added. See Figure 5

Note the grouping added to the left of screen. These allow you to hide the detail rows and just see the totals. See Figure 6

The top image in Figure 6 is displayed by clicking the small 2 (top left). The bottom image is displayed by clicking the small 1 (top left).

Figure 5

Figure 5

The plus and minus icons allow you to show and hide rows quickly. The small numbers at the top left allow you to show and hide all the detail rows in one click.

Note the Remove All button in Figure 4 - this will remove all the subtotals and return the list to the layout in Figure 2.

Try the sample spreadsheet

Figure 6

Figure 6

Neale Blackwood CPA is CPA Australia’s resident Excel expert. He runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to [email protected].

How-to articles, sample spreadsheets and more

Excel yourself

Combine annual and monthly data in Excel with these simple steps

Making data relationships work

Easy steps to make data Relationships work in Excel

Excel Yourself

Easy tips for matching salary to job class in Excel