Adding a cumulative sum column to a pivot table

Question

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,000

I 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?

Answer 1

Have a look at this topic on the Contextures site:

Excel Pivot Table Tutorial -- Running Totals

Answer 2

Can'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.

Answer 3

Running 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.

Answer 4

I would up the value of this question because I want a better answer than this:

1. Build your pivot table
2. Select all of the values
3. Paste special in a new sheet "Values Only"
4. Add a "summing column" that adds previous to current
5. 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
...

Answer 5

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.