Pivot tables in Excel are useful for summarizing data. But you can do more than just simple summations and can also calculate running totals based on multiple fields. In the spreadsheet which I’ll use for this example, I have sale by month and by category.
Creating and setting up the pivot table
The first step is to create the pivot table. This can be done by just clicking anywhere on the data set and going to the Insert tab and clicking on Pivot Table. I’ll put the dates in the Row section and the sales field in the Values section. I’ll insert the sales field a second time so that I can have one value for the raw monthly sales alongside the running total. Here’s what it looks like thus far:
To create a running total, I’m going to right-click on the second sales column and select Value Field Settings. Next, in the Show Values As tab I’ll select Running Total In and use Month as my base field. I’ll also rename the field to say Running Total:
This sets up the pivot table to show my total sales alongside the running total. A good way to check to see that it is correct is to see that your grand total in the original sales field matches the last value in the running total:
You can also have running totals reset based on the category. In my data, I have electronics and furniture sales. To break it down by those sections, I’ll add the Category field above the Month field in the Rows section. Now I have the running totals broken out by category while still tracking year-to-date values.
This format may be a bit confusing since the subtotals are above the data. What you may want to do is move the subtotals to the bottom of each section, so that it’s easier to compare them against the running totals to make sure they match up. To do this, click on the pivot table to activate the Design tab. Within there, there is a drop-down option for Subtotals where you can select to Show all Subtotals at Bottom of Group.
Upon selecting that option, the totals will now appear at the bottom.
If you like this post on How to Calculate Running Totals in a Pivot Table, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.