Using 1 pivot table for more than 1 tab

Problem definition:

For my sales analyses I use for the last 3 years a separat tab for the database per year. Per year I have defined the same pivot table in order to seen sales per product group, customer, scustomer group etc.

Wheneven I want to compare the data between diferent years I must do the same selection for the respective years and it can happen that not all the same products are sold in all years so it can happen that the 3 pivot tables have not the same format.

Question:

Is there a way top combine the 3 pivot tables into 1 ?

Thanks a lot for your help,

Fred

To get all three years into one pivot table, you will have to use a Multiple Consolidation Ranges pivot table. A Mulitple Consolidation Pivot table does have some limiting rules.  The most disrupting rule is that  a multiple Consolidation  Pivot table will take only one Row field.  Meaning you can have only one column of text data before your Numeric/Measure data.

1.  Start a new workbook and create three tabs (one for each year).

2.  On each Year's tab, Copy the respective dataset.

3.  Remove all columns that are not ProductName or a numeric field.  For example, if you have Region, State, Market, etc, Delete them.  The idea is to have a table that ProdutName field and other numeric fields (Revenue, Units Solds, Price, etc)

4.  Go up to the Excel main menu and select Data|PivotTables

5.  In the Pivot Table Wizard, select Multiple Consolidate Ranges and click next

6.  In the next window choos "I will create the Page Fields"

7.  In the next dialog box, you will define the ranges to bring in.  Select your first Year's dataset, then click "Add". 

8.  Under the choice "How many Page Fields to you want" Select 1.

9.  Enter the Year in the Field On input box.  This would be the year number that is associated with the first dataset you added. For example, enter 2006.

10.  Select your second Year's dataset, then click "Add". 

11.  Enter the year number that is associated with the second dataset.  (it's ok to overwrite the value you entered here before)

10.  Select your third Year's dataset, then click "Add". 

11.  Enter the year number that is associated with the third dataset. 

12. Click Finish.

Your result will be a pivot table that contains your ProductNames and Measures.  You can switch from year to year using the Page1 filter that has been created.  You can now rename the Page1 filter to "Year" and drag it around like any other field.  At this point, you can analyze the Product information from year to year all in one pivot table. 

Of course life would easier if you could bring this all into Access and analyze it there