asked on

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

Hi,

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!

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Hello jsimonuchc,

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

=Total/SUM(Total)

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.

Regards,

teylyn

jsimonuchc

ASKER

I should have mentioned the other caveat. I can't modify the source data. (Client's will be providing me this data as is)

parasharabhishek

Can you paste a screenshot of pivot table that you have.

jsimonuchc

ASKER

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,

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jsimonuchc

ASKER

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