Creating a Subtotal Report

Creating a Subtotal Report

One of the few instant – or relatively instant – reports that Excel provides is the Subtotal Report. Shown here applied to a table of insurance policies and their related numbers, the report allows you to take your sorted data, which created groups of records, and then apply functions such as summing, averaging, and counting to those grouped records – based on any number or currency fields within those records.

Note that I said the data needed to be sorted first – and that I referred to the data as a list, not a table. If your data is stored as a table, it needs to be converted to a range in order to create a subtotal report. The reason? When a range or list is converted to a table, formatting and functionality is added to it that conflicts with the subtotal report tool. You can always convert back to a table after you’ve viewed and derived information from your subtotal report.

And why must it be sorted first? In the subtotaled list you see onscreen, I’ve sorted the list by the State field. And then within the States, by the type of Construction of the insured buildings. So we’ve created 2 groups through those sorts, which means I can create meaningful subtotals, using the numeric data in the list – such as the number of policies held in each state and the total insured values for each type of construction.

One of the best things about the Subtotal report is the ability to expand and collapse the sections within it. Note the additional columns on the left – each one resulted from the creation of a subtotal within the report, and that means that each subtotaled field can be collapsed into totals or expanded to show details, as shown as I click the 1, 2, 3, and 4 at the top of those columns. 1 gives a single grand total for the first subtotaled group I created, 2 gives me subtotals for the first group, 3 gives subtotals for both fields I used, and 4 gives me all my detail, plus embedded subtotals between sorted groups. The more subtotaling you do, the more levels you’ll see here, represented by numbered columns.

I can also click the plus and minus signs in the numbered columns to expand and collapse certain sections of the report.

Now let’s create this subtotal report from scratch, step by step.

First, I’ll make sure my data is seen by Excel as a list, or range, as I mentioned earlier. On the Table Design tab, which is only visible if your list is currently seen as a table by Excel, I click Convert to Range. Don’t worry – you’ll still be able to convert it back to being a table if you need to, and ranges can always be sorted and filtered, so you don’t lose basic database functionality while it’s in range mode.

Next, I click inside the data, on any cell in any row, and I’m going to use the Sort button on the Data tab to sort my records in preparation for subtotaling on the sorted fields. I’ll sort by State, and then using the Add Level button, sort by Construction. Both sorts are in A to Z order.

Closing the Sort dialog box, I can see my groups are created. Within each state, my records are sorted by the type of construction used in the insured building.

Now I can create my Subtotal Report, using the Subtotal button on the Data tab.

In the resulting dialog box, I start by choosing the field associated with my main sort group, which is State, from the “At Each Change In” list.

Then, I choose what operation I want to perform from the Use Function list – and I’ll Count the number of records in each State, so I make sure only State is checked in the Add Subtotal To list.

Finally, because I know I’m going to add another layer of subtotals and I will want to keep the ones I’m making now, I’ll uncheck the Replace Current Subtotals box.

Then I click OK, and my first set of Subtotals is created. I’ll click the 2 in the new columns on the left, and you can see a total number of records for each state in the list. When I click the 1, I see just the grand total, and 3 restores all the details. When you’re in Level 2 view, note the plus signs – you can use them to expand individual states’ details (or the details of whatever you’re sorting by).

Clicking Subtotal again, I can add the second level of subtotals, this time choosing At Each Change in Construction and adding a Sum to that field only. Make sure Replace Current Subtotals is still unchecked.

Now I can add the totals for the insured amount that’s attributed to each type of construction, within each state – by changing At Each Change In to Construction, changing Count to Sum, and making sure only Insured Value is checked in the Add Subtotal To list.

I’ll click the 3 now to provide the 2 levels of subtotals, without the details, and use the 1 and 2 to see more or less info, as well as using the plus (and then minus) signs to expand and collapse the report by subtotaled field. 4, of course, brings back all of the details.

If you want to share a view of your report in any of its many expanded and collapsed views, just use the Save A Copy command on the File tab and choose PDF as the file format to save. You can then send the report to anyone by email or store on a shared drive.

When you’re ready to restore your data to its un-subtotaled state, just click the Subtotal button again and click the Remove All button.