• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

pivot tables - excel 2003

megs

New Member
Is there a way to format a pivot table setup to calculate a variance in place of the Grand Total colum ? - eg TY vs LY comparison analysis.tal column.


WEEK NO 2009 2010 Grand Total

1 4313.12 4313.12

2 4316.88 4316.88

3 5513.01 5513.01

4 4728.37 4728.37

5 4438.43 4438.43

6 5517.71 5517.71

7 3002.5 3002.5

8 4312.07 4312.07

9 3975.16 3975.16

10 6466.6 4320.78 10787.38

11 6368.04 6368.04

12 6368.04 1790.71 8158.75
 

Hui

Excel Ninja
Staff member
Assuming your using Excel 2007

and that you have a Pivot Table with Year Headings as 2009 and 2010

Click on a Column Label in the Pivot Table

On the Ribbon select Options, Formulas, calculated Item

Type in a Name for your new field in the Name box ie: TY v LY

In the Formula Box use ='2010'-'2009'

Next Right click on the Grand Total header and select Remove Grand Total

Voila
 

megs

New Member
Thanks for this Hui, but I am unfortunately still working with Excel 2003! Can't wait to upgrade...

Any tips on the old version?
 

keymaster

New Member
I am not sure if I understood the problem. but here is how you can findout TY vs. LY


right click on any field, go to "field settings". Click on the "Options >>" button. Now, from the "Show data as", select "Difference from".

Set "Base field" as "Year", "Base item" as "(previous)" and you are done.


Now you can remove the grand total.
 
Top