Excel Pivot calculated field question

[GC102014]

[TABLE="width: 520"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 4"]I have table that has the same account names but for different years (sample below). I want to create a pivot item field that calculates the difference between the years for the same account name[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account Name[/TD]
[TD]Year[/TD]
[TD]Amount[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account 1[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]15000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account 1[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]20000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account 2[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]30000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account 2[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]60000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]So for Account 1 the difference is 5K and coount 2 the difference is 30K. How do i get the difference for the two fields when the amounts belong to the same column and can only be differentiated by year?[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sum of Amount[/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]2015[/TD]
[TD] Grand Total[/TD]
[/TR]
[TR]
[TD]Account 1[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]35000[/TD]
[/TR]
[TR]
[TD]Account 2[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]90000[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]45000[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]125000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]The output I would like to show is below:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]I need the change to be part of the pivot table because I want to then get the top 40 base on change column.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sum of Amount[/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]2015[/TD]
[TD]Change[/TD]
[/TR]
[TR]
[TD]Account 2[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]30000[/TD]
[/TR]
[TR]
[TD]Account 1[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]45000[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]35000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Please help![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[wynn89]

Hi,

is it possible to pivot your original data into this format

[TABLE="class: px"cms_table""]
<tbody>[TR="class: cms_table"]
[TD]Account Name[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]2015[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Copy and paste this format into a new sheet and we can calculate the change from there by adding a column to the right of "2015"

Regards,
Wynn

 

[GC102014]

I can definitely just add an extra column and just deduct 2015 from the 2015 total but the problem is I need to show top 40 result based on the changed field. Since it is not part of the pivot table it won't sort by the change field

 

[wynn89]

I can definitely just add an extra column and just deduct 2015 from the 2015 total but the problem is I need to show top 40 result based on the changed field. Since it is not part of the pivot table it won't sort by the change field

Hi,

after adding an extra column and getting the change field, format the range into a table and use the "Number Filter" function in the table , click on "Top 10" and filter it to top 40 items

Regards,
Wynn

 

[GC102014]

Hi Wynn,

I've highlighted the pivot table and the "change" column that I've created then did a control L. It did not convert it into table. Any tip on how to do this? So close! Thank You!

 

[wynn89]

Hi GC,

Basically these are the columns you require in a sheet (not pivot table)

Account Name 2014 2015 Change

then change this range into a table and use the number filter

Regards,
Wynn

 

[GC102014]

gotcha. thanks for the tip. it's a good workaround. i just wished though that I can do this via pivot so the users can click on any part pf the pivot and see the details.