Subtotals could either refer to the =SUBTOTAL function that is typed into a cell or the Subtotal tool found in the DATA tab. This post addresses the latter, namely the Subtotal tool, that automatically groups rows based on changes in one column, and adds, counts, etc. the values in each group.
Adding subtotals is a quick and easy tool for when you need to take some raw data and group it by a certain field, e.g. by supplier reference, general ledger account code, etc.
In this example we have a listing extracted for a bunch of expense transactions and need to analyse the transactions for each project. We don’t want to format it in any particular way, we really just need to analyse the data (sum amounts by project, get rid of transactions that cancel each other out, etc). With a quick two-step process we can turn this block of raw data into something useful. So here goes!
Step 1. Auto-filter and sort by project code
For subtotals to work properly we need our data to be grouped by the relevant field. In this case we need to look at the transactions for each project, so we need to sort by the project column. We can apply an Autofilter by selecting the block of data (including headings) and clicking on the Filter button in the DATA tab:
Now we select the dropdown in the Project column and sort by project:
Our data is now ready to be grouped using the Subtotal tool.
2. Insert subtotals
Select the entire dataset again, including headings, and click the Subtotal icon in the DATA tab:
Now select the following:
At each change in: Project Code
Use function: Sum
Add subtotal to: AMT1 (As this is the amount column in our dataset)
Ok, what exactly does this all mean?
Well, in the previous step we sorted the dataset by project, so all the transactions for each project are nicely listed together. Now we’re just telling Excel to run through the list from the top and sum up all the entries in the Amount column, every time the “Project” field changes. It sounds complicated, but once you get the concept it’s pretty simple. Just step through the criteria one by one.
And that’s it! Now you can clearly see the transactions that relate to each project and identify duplicates, errors, etc.
By clicking the numbers on the far left (marked with a 3 in the image above) you can collapse the entire field and only show the subtotals, or even just the grand total:
Play around with it, you can always remove the subtotals by clicking on the Remove All button:
Have questions? Drop them in the Comments below!
