Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Calculations
I frequently use pivot tables to analyze cost variances from one month to the
next. Is there a way to change the total column to show the difference between the two months rather than the total of the two. I currently copy/paste special/value the pivot table to a new sheet and then use formulas to manipulate the data. Is there a better way using pivot table functions? Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Calculations
Hi Cherly,
Place the cursor inside the table, then do the following Go to Pivot table toolbar-Select Pivot Table Drop down list-Go to Formulas-Select calculated Fields Then Insert Calculated Field Window appears At filed Name: Difference (write the field name you want, here i taken difference) At filed Formula: (Do like this)= double click on present month filed from fields list -(minus sign) double click on pervious month Click on Add button select "Difference" from fields list Click on Insert filed This Difference field will be added in the pivot table and shows differences between present month value and previous month value (which you selected at formula) Regards, Vinod "CherylC" wrote: I frequently use pivot tables to analyze cost variances from one month to the next. Is there a way to change the total column to show the difference between the two months rather than the total of the two. I currently copy/paste special/value the pivot table to a new sheet and then use formulas to manipulate the data. Is there a better way using pivot table functions? Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Calculations
Hi Cheryl
Right click on PTTable Optionsuncheck Grand Total by Row That will get rid of your Total column. Right click on PTPTWizardLayoutgrab your data field and add to the data area a second time double click field labelchoose SumShow Data asDifference From Base field choose your Data filledBase ItemPreviousOKOKFinish On the PT, if the difference is showing on a separate line, as opposed to a column, then drag the data icon across to Total You will now have columns for the 1st Month, 1st Month Difference, 2nd Month, 2nd Month Difference. The column for 1st Month difference will always be blank, as there cannot be a difference from itself, so you can Hide that column if required. -- Regards Roger Govier "CherylC" wrote in message ... I frequently use pivot tables to analyze cost variances from one month to the next. Is there a way to change the total column to show the difference between the two months rather than the total of the two. I currently copy/paste special/value the pivot table to a new sheet and then use formulas to manipulate the data. Is there a better way using pivot table functions? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Calculations | Excel Discussion (Misc queries) | |||
Pivot table Calculations | Excel Discussion (Misc queries) | |||
Pivot Table Calculations | Excel Discussion (Misc queries) | |||
Pivot Table Calculations | Excel Discussion (Misc queries) | |||
Calculations in pivot table | Excel Worksheet Functions |