I have a pivot table where I am representing a series of data across a number of categories as follows:
Sum of amount Area
Status Priority Project Department 1 Department 2 Grand Total
In-flight 1a P1 10,000 10,000 1b P2 30,000 30,000 2 P3 28,000 28,000 3 P4 15,000 15,000
In-flight total 55,000 28,000 83,000
Pending 4 P5 16,000 16,000 5 P6 1,000 1,000 6 P7 30,000 30,000
Pending total 46,000 1,000 47,000
Grand Total 101,000 29,000 130,000I would like to add a column on the end of the pivot table that shows a cumulative running total of the Grand Total column amounts. Ideally I would like to preserve the pivot table properties, i.e. I would like to add this cumulative sum column and still be able to add and remove fields from the table. Is this possible?
25 Answers
Have a look at this topic on the Contextures site:
Excel Pivot Table Tutorial -- Running Totals
1Can't reply to your comment in Doug's post as I don't have enough points, but just drag the field to the data area alongside the running total column. You can have the same field appear more than once on a pivot table.
2Running Total is a built in function for Pivot Tables.
Under Value Filed Settings you can find the settings you need.
On the Summarize Values By tab, Select SUM.
Then on Show Values As tab select Running Total In, And Select the column header you wish to Group By.
I would up the value of this question because I want a better answer than this:
- Build your pivot table
- Select all of the values
- Paste special in a new sheet "Values Only"
- Add a "summing column" that adds previous to current
- Chart it!
Below is a TR submit date along with the total # of TRs for that date which the pivot table did wonderfully. Unfortunately, I wanted to see the sum over time (well, quarterly)... The procedure above got me there... curmudgeonly.
Submit_Date <--from pivot--> Total Sum <<-- Handmade Summing Column
April 1, 2012 8:00:00 PM EDT 1 1
April 1, 2013 12:24:54 PM EDT 1 2
April 1, 2013 2:35:57 PM EDT 1 3
April 1, 2013 8:00:00 PM EDT 1 4
April 1, 2013 8:42:29 AM EDT 1 5
April 1, 2014 1:49:07 PM EDT 1 6
April 1, 2014 8:26:00 AM EDT 1 7
April 1, 2014 9:42:25 AM EDT 1 8
...You can add the same column multiple times to the pivot table, so to address your requirement, you can add the column once as 'Normal', and then add the column to the pivot table again, this time using the 'Show Values As Running Total' feature on the second instance of the column. That will allow you to see both the normal amount and the running total.
