It’s easy to create a running total in a pivot table, and it’s usually used to show how quantities accumulate over time.
In this example, there are three Value fields in the pivot table, showing the number of units sold on each date.
- In column B, the Sum of Units is shown, with no calculation. This is the number of units sold on each date listed.
- In column C, the Sum of Units is shown, as a Running Total for Date. This is the total units sold, up to and including each date.
- In column D, the Sum of Units is shown, as % Running Total for Date (new in Excel 2010). This is the total units sold, up to and including each date, divided by the grand total of units sold.
Running Total for Date
When you select either Running Total In or % Running Total In, you have to select a Base Field. The running totals will be accumulated at each change in that Base Field.
We want a running total down the list of dates, so Date is the Base field in this example.
By November 1st, a running total of 399 units have been sold, and the % Running Total is 18.8% of the 2121 overall total units sold.
End of Year Problems
The running total works for a continuous list of dates, but doesn’t flow down the whole list if you group the dates by years and months. In the pivot table shown below, the date has been grouped by Years and Months.
Now the running totals stop at December 2012, and start again in January 2013.
It can be a little difficult to understand the running totals in this layout, so be sure to label the pivot table headings, or add a title to explain it.