Have you ever had hundreds or even thousands of rows that you had to subtotal? If you use Excel at work, chances are that you have. Did you enjoy calculating those subtotals? Chances are that you haven’t Believe it or not, I don’t mind subtotaling, and sometimes I even like it! Why? How? Because I know of some really neat and fast ways to subtotal, and I’ll show them to you!
There are quite a few ways to subtotal in Excel. Some involve formulas, some involve preset tools, and some are better than others. In today’s post, I’ll discuss the subtotal tool which you may or may not already know about. This method is OK, but I prefer subtotaling with pivot tables. The subtotal tool is good for simple and straightforward subtotals, but as soon as you need some flexibility, pivot tables are much better. I’ll show you how to subtotal with pivot tables in next week’s post, but you should probably learn about subtotal tool first since it’s a nice built-in feature in Excel.
Let’s say you have some hypothetical spending data for the months of October, November, and December of 2013. Download my example to follow along. How can you see subtotals for your spending for each month?
First, make sure your data is sorted by month. Then select the entire set of data including the header row.
Then go to the Subtotal button in the ribbon.
When the menu pops up, select the following criteria. 1) At each change in: MONTH (since we want to see the subtotals by month), 2) Use function: Sum (since we want to add the amounts together), and 3) Add subtotal to: AMOUNT (since amount is what we want subtotaled). You can leave the check marks as-is. Then click OK.
Now you should see these little boxes in the upper left-hand corner with numbers in them. This is how you know your subtotaling worked.
If you click on the “1,” you’ll see just the Grand Total.
If you click on the “2,” you’ll see subtotals by month.
If you click on the “3,” you will see all your data again.
So, that’s not so bad, is it? Well, this method might be easy for one level of subtotaling…but what happens if you want to add multiple levels of subtotals?
Let’s try adding CATEGORY subtotals within each MONTH subtotal. First you would have to remove your month subtotals and start over. This is because your data needs to be sorted appropriately first. If you want to subtotal by month, and then by category within each month, you have to first sort your data by month and then by category.
To remove your subtotals, select your entire data set, and click the Subtotal button again.
When the menu pops up, select Remove All.
Now that your subtotals are gone, resort your data by Month first and then by Category. Then, follow the previous steps above to re-do your subtotals by Month.
Once your Month subtotals are back in, select your data again, making sure you highlight the header row, all the way to the bottom of your data.
Go to the Subtotal button, and when the menu pops up, enter the following criteria. 1) At each change in: CATEGORY, 2) Use function: Sum, and 3) Add subtotal to: AMOUNT. This time, make sure the “Replace current subtotals” option is checked off. This is how you tell Excel to “nest” the subtotals, putting one subtotal within the other. Click OK.
Now, you should see 1, 2, 3, and 4 in the upper left-hand corner.
When you click the “1,” or the “2,” you’ll see the same subtotals as before (Grand Total, and subtotals by month respectively). But this time when you click “3” you’ll see subtotals by Category, and then by Month.
You might need some practice to fully master subtotal tool. It works, but it’s not the most intuitive feature, and requires some prior steps such as sorting.
Come back next week, I’ll show you my favorite way of subtotaling