How do I get a pivot chart to show me a running total as a percentage

jsimonuchc used Ask the Experts™

I am trying to figure out a way to get a pivot chart to give me a running total as a percentage. I know how to make it give me a running total as a count, but I can't seem to make it do it as a percentage. For example, this is what I would like to do with my pivot table.

 Item                 Count            Running Total            Running Total Percent
Oranges            25            25                          21.19%
Apples            51            76                            64.41%
Peaches            21            97                          82.20%      
Grapes            16            113                          95.76%
Bananas            5            118                         100.00%

Total            118            

I would like to do this without VBA. Thanks!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Awarded 2010

Hello jsimonuchc,

You could add a 'Running Total Percent' field to your source data with the formula


Then add the field '% of Total' in your PivotTable and use the "Running Total in" option as you did for your Running Total field. Format as 0%.
Be sure to Enable selection before applying this format so that it will persist after Refresh.




I should have mentioned the other caveat. I can't modify the source data. (Client's will be providing me this data as is)
Can you paste a screenshot of pivot table that you have.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!


Unfortunately, I cannot. The pivot table i have has protected data on it. The sample I provided is a very close approximation of what the pivot table looks like,
Top Expert 2010
Hello jsimonuchc,

Unfortunately, the answer is that this cannot be done within the PivotTable.  You would have to use a formua
outside the PivotTable to do this, which would of course mean that if the PivotTable resizes your formulas
could be wiped out or become outdated.  That risk, however, could be mitigated by using VBA code on the
worksheet's PivotTableUpdate event.




Ok Thanks. I think they address this problem in Excel 2010.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial