Subtotaling with Pivot Tables
Ask Excel experts what their favorite tool is, and many of them will say pivot tables. But why exactly are pivot tables so awesome? There are many reasons actually, that I will probably need to discuss over several posts, but I will talk about my favorite reason on this post. And that reason is quick subtotaling.
The other day I was teaching my colleague about how to create pivot tables, for the sole purpose of creating quick subtotals. She had to put together an urgent report that probably would have taken her half the day, if not the full day, if I hadn’t taught her my pivot table trick. With a pivot table, she made the report in a matter of minutes. Like 15-20 minutes. And the pivot table part probably took her 3 minutes.
In my last post, I talked about how to use the built-in subtotal tool. While you can always use that for subtotaling, let me show you why pivot tables are better.
Pivot Table Example
Let’s use the same hypothetical spending data as before, for the months of October, November, and December of 2013. I’ll show you how to use a pivot table to first create subtotals by month. Download my example to follow along.
First select your entire data set, including the header row, all the way to the bottom (row 142). FYI, there must be an actual header for each column for the pivot table to work.
Then go to your ribbon and click on the Insert tab. Then click the Pivot Table button.
A dialog box will appear, asking you where your data is. Since you’ve previously highlighted your data set prior to clicking the pivot table button, Excel correctly guessed where your data is. You can click OK.
A new tab gets created, and you will see something that looks like this on the left side of the sheet.
You will also see something like this on the right side of the sheet. In the top box, each of your headers is listed. These headers are now called “fields,” and you can drag them into the bottom boxes to create your pivot table.
To subtotal by MONTH, all you have to do is click and drag the MONTH field…
Into the box called “Row Labels” towards the bottom.
Then drag AMOUNT into the “Values” box.
As soon as you did that, look what happened to that thing on the left part of your screen! A pivot table was created, with subtotals by month! That was pretty fast right?
Now for the really cool part…
Remember how many steps it took to created a nested subtotal with the subtotal tool? Watch how easy it is with pivot tables. Let’s add subtotals by CATEGORY within each MONTH.
Simply drag the CATEGORY field from the top box into the Row Labels box, below the MONTH field.
Done. Look at your pivot table now!
If you wanted to add SUBCATEGORY as well, all you have to do is drag SUBCATEGORY right below CATEGORY. It’s that simple!
It gets even better…
What happens if you realize you want to see your subtotals by CATEGORY then MONTH, instead of MONTH then CATEGORY?
All you have to do is take the MONTH label in the Row box, and drag it below CATEGORY, so the label order is switched.
That’s it! So easy right?!
It’s super easy to “pivot” your view, simply by dragging the fields around.
I hope you now understand one of the reasons why people love pivot tables so much. Play around with them, you’ll love them too!