Debra, That is just awesome! A followup question if I may. As mentioned I
have aprox 70 fields in this table, some of those are components of total
sales, ie.. residential sales, commercial sales etc.. Is their a way to
dynamically select the sales component field from a list external to the
pivot table and have the variance and % of variance calculate on that field
just as you described below, similar to an INDIRECT function/pointer?

"Debra Dalgleish" wrote:

Create a pivot table with Year in the column area, and sales in the data
area.
In the Pivot Table Wizard, add another copy of the Sales field to the
data area.
Double-click on the second Sales button
Click the Options button
From the Show Data As dropdown, choose Difference From
As the BaseField, select SalesYear
In the Base item list, select (previous)
Click OK

Add a third copy of the Sales field to the data area.
Double-click on the third Sales button
Click the Options button
From the Show Data As dropdown, choose % Difference From
As the BaseField, select SalesYear
In the Base item list, select (previous)
Click OK, click OK, click Finish


CraigS wrote:
I have a sales application that contains tables of approx 70 fields with
sales data for each year, one of which is a year field. I'd love to combine
the tables into a single table so that I can represent the data in a Pivot
Table, but need to figure out how to show, create, calculate a variance
field, and a % variance field in a pivot table if I do combine the tables.
In the Pivot table I'd want to show:
2004 Sales 2005 Sales Variance % Variance
10 12 2 20%

Note: The Sales field is the same field, just a different year

Any Suggestions?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html