Pivot Table Calcs

[Guest]

Hello,

I am trying to add a calc field to my pivot table. My concern is if I add
the calc field into excel and not part of the pivot. Each Time I refresh the
data the pivot will overlap the calc column and I don't want each line item
to have it's own calc. it would make my report way to big. Is there a way to
add the calcs? Thahks!!

----Pivot Results--------------- --------Calc I would like to Add-------
Agency Jan Feb GrandTotal Current Month % to Total Grand Total
%
ABC 123 321 444 16%
13%
SCS 456 654 1110 33%
33%
EGA 789 987 1776 50%
53%
Total 1368 1962 3330 100%
100%

 

[Bernie Deitrick]

carolini,

Add a calculated field to your pivot table. Select your pivot table, then on the PT commandbar,
select "Pivot Table", "Formulas", "Calculated Field..."

Then give the field a name like "GrandTotal", and use the formula

= Jan + Feb

(You can use the "Insert field" button to create the formula)

Then press OK, then drag that field to the pivot table data area. Then drag Feb to the data table
area again, and select a cell in that new column, and right-click and select "Field settings".
Click the option button, and change "Show data as" to "% of column". Do the same thing again for
your "GrandTotal" calculated field, and you will get the exact table that you are looking for.

If you have any problems, post back with your email address, and I will send you a working example.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thank you for your quick response. I can't calculate Jan + Feb since it is
in a month field, I just put it there as an example. I see where you are
going with the "show data as" but can you send me a working example.

Thanks,
(e-mail address removed)

 

[Guest]

Thank you for the working example!!

 

[Bernie Deitrick]

You're welcome!

HTH,
Bernie
MS Excel MVP