Percentages in Pivot Tables?

[Phil M]

I've been flicking through the help menus and the Excel 2003 Bible, plus some other links courtesy of here and can't see how to do this.

I have a pivot table summarising categories down the left on each row, with totals from another category on the horizontal axis from left to right - with a total of the row as the last cell (sorry I can't post a screenshot).

I would like to show the proportion of each category total as a percentage of the row total - to the right of each number (eg 2 50% 1 25% etc).

I got as far as finding the "% of row" function in the help pages and the "formulas>calculated field" dialog box from the pivot table tool bar but can't work out how to put in a formula to do what I want - I realise cell references aren't accepted.

Can anyone help please (assuming you can make sense of my description!)?

Thanks in advance!

 

[Norie]

I don't think you need a calculated field.

Add the data you want the percentage for twice and use the % of for one of them.

 

[Phil M]

Norie - add it in the source data you mean?

Not quite sure I understand - where would I put in "% of"?

 

[Norie]

Phil

Add it as a data item twice.

Then right click on one of them and select Field Settings... and then Options.

Choose from the dropdown '% of row'.

I'm not 100% sure if that will give you exactly what you want, but I'm sure if can play about with the available options.

 

[Phil M]

Cheers for that- I'll have a play about! :D

 

[Phil M]

Well, I've been able to show the totals there as %ages, or insert a second row underneath to show the total in the cell above as a %age, but not have a %age as a column alongside each total. I should be able to work with this and copy and paste everything out to a separate table in the format I want for circulation purposes at least.

Ta!