This is a repost of an advanced Pivot Table question

Posted by Paul Johnson on December 12, 2001 6:15 AM

How can you calculate the % of an item within a Pivot table. The tricky part is that I want a % of the subtotal.

In a past week's tip it was shown how to calculate as a percentage of the whole. But, (using the example) I would like to know the % apples are of Central. (see link below)

Mark repsonded with - Use % Of with 'Region' as you Base field and "Central" as your Base item (nt) - Mark W. 14:03:38 12/10/01 (0)
You help is appreciated...e-mailed responses are appreciated.

This works for just one item - But - What if I want to show the percentage of total for all of the items as opposed to the quantity.

Thanks again for your help..... PJ

Posted by Mark W. on December 14, 2001 2:47 PM

Suppose your data in cells A1:C5 looks like...

{"Fruit","Region","Amount"
;"Apples","Central",10
;"Oranges","Central",25
;"Bananas","Southern",19
;"Oranges","Southern",33}

Follow these steps...

1. Enter the field name, '% of Region', into cell
D1.
2. Enter the formula, =C2/SUMIF($B$2:$B$5,B2,$C$2:$C$5),
into cell D2 and Copy down to cell D5.
3. Create a PivotTable with 'Sum of % of Region'
in the DATA area (and change its field name to
"% of Region "), and both 'Region' and 'Fruit' in
the ROW Area ('Region' should be the left-most
field).
4. Uncheck the "Grand totals for columns" PivotTable
Option.
5. Right click on the PivotTable's "Total" label
and choose the Select | Enable Selection command
from the popup menu.
6. Repeat step 5 and choose the Select | Data
command from the popup menu.
7. Right click on the selected cells and choose
the Format Cells... command from the popup menu,
choose an appropriate Percentage format, and press
[ OK ].