Simple Pivot Table - how to add column that converts Count figures to percentages of Grand Total?

[Ironman]

Hi

I hope you can help?

I have a very simple pivot table based on a single column in another sheet, extract below.

DialColour
Grey
White
Black
Black
Black
Black
Gold
Black

Below is the simple pivot table I have created:

Dial Colour	Count
Black	300​
Black(out)	3​
Black/Grey	6​
Black/Silver	2​
Black/White	2​
Black/Yellow	1​
Blue	7​
Blue/Black	1​
Brown	1​
Camouflage	1​
Copper	1​
Cream	19​
Gold	2​
Green	7​
Grey	32​
Grey (two-tone)	4​
Grey/Black	3​
Grey/White	1​
Orange	1​
Red	4​
Salmon	1​
Silver	12​
Teal	1​
White	80​
White (Panda)	9​
White/Gold	1​
Yellow	2​
Grand Total	504​

I'm trying to create a third column in the pivot table that converts each of the figures in the second column to a percentage of the Grand Total. I've tried but all I can do is replace the figures in the second column with percentages of the Grand Total - I can't create both.

Help much appreciated!

 

[RoryA]

Add the column to the values area again and then right click it and choose Show Values As, Percentage of Column.

 

[Ironman]

Thanks for replying Rory - I've tried what you've suggested, which is the same as what happened when I tried this myself i.e. it just replaces the values in the second column with percentages, but I need a third column for this, with the second column displaying the values. I can't see any option to create a third column.

 

[Alex Blakenburg]

Then you are not adding the DialColour to the Values field a 2nd time

Add the column to the values area again

See below.

 

[Ironman]

Hi Alex, thanks for helping.

I wish I could get the second Value you've circled - I had tried to add the DialColour column again, but the only way I could try to do that was to create a new Pivot Table. Sorry for being slow, I've never used this function before (I didn't add the DialColour to the Values field, it was done for me by Excel)

 

[Alex Blakenburg]

When you click inside the pivot table do you see something like this ?
Perhaps show a screen shot of what you see ? It should be a panel on the right side of excel (as in my previous screenshot)

 

[Ironman]

Yes, exactly as per your screenshot.

Edit: Sorted! I right clicked DialColour in PivotTable Fields

 

[Alex Blakenburg]

Glad it is working now, thanks for letting us know.
Pivot tables are one of the most useful features in Excel and well worth coming to grips with.

You might find this page on the contextures site helpful.
How to Set Up Excel Pivot Table for Beginners

 

[Ironman]

And thank you Alex for going the extra mile for me

 

[Alex Blakenburg]

Just answering another question on a similar topic and you might find one of these helpful both around 13 mins