% of category total

Hi All,

I want to find a quicker way to make a % of category total, below is an example to help illustrate my question.

Red Apples 3 33%
Green Apples 5 56%
Pink Apples 1 11%
Apples Total 9
Green Pear 4 40%
Red Pear 6 60%
Pears Total 10
Red Grapes 7 25%
Green Grapes 9 32%
Mixed Grapes 12 43%
Grapes Total 28

At the moment I type the formula "=B1/$B$4", then drag up to the end where I see a blank, and then repeat. Surely there is a quicker method, I would be very grateful if you could help with an answer or even point me in the right direction.

Thanks in advance

Hi All,

So B4, is the total category of items sold per week (all bold numbers are the totals sold per week). What I would like to know is how many red apples are sold as a % of total apples per week.
But I wish to use a method where I could speed this process up for all types of fruit, instead of manually typing C2=B2/$B$5, then dragging this down until I see a category total.

Not all categories have the same number of items so this may be a little more tricky.

Any help would be greatly appreciated. once again if anything is missing or requires further information please do not hesitate to ask.

Thanks in advance.

Thanks Richard,

This is exactly what I needed.

so I can adjust the values by going to Pivot Table Options> Value Field Settings> (Tab) Show Values As> Select % of Parent Row Total.

This will save me lots of time in the future!

Apologies for the delayed reply, I have been away on holiday.

By adjusting, I meant the way the numbers are formatted. i.e. whole numbers, or %s per parent line or entire table etc.

The dynamic range name seems like a great tool to use. Thanks for the additional information I will try to incorporate that on my next set.

Thank you all!

Apologies for the delayed reply, I have been away on holiday.

By adjusting, I meant the way the numbers are formatted. i.e. whole numbers, or %s per parent line or entire table etc.

The dynamic range name seems like a great tool to use. Thanks for the additional information I will try to incorporate that on my next set.

Thank you all!