Pivot table-change data source

[lmusser]

I built a pivot table in Workbook 1, then a new version was created. So I copied the pivot table to Workbook 2. But now the data source for the table is still in Workbook 1. The data is basically the same in both workbooks except that workbook 2 is the one that I will be editing and adding data into. So my question is how do I change that data source to the sheet in workbook 2? Thanks

 

[BjornS]

Hi,
In Excel 2003 you right click the pivot table in book2. Then choose the "Pivot table wizard/guide" from the menu (I am unsure of the English name here, since I use a Swedish Excel version).

Press "Previous". Now you can change the data source to the cells with data in book2 (which must have the same structure)

Kind regards
Bjorn

 

[lmusser]

I should have mentioned that I am working with Excel 2007. Bjorn, it doesn't seem to be the same as Excel 2003. Thanks.

 

[BjornS]

Hi again,
OK then, in Excel 2007, do the following.

Click anywhere on the pivottable.
The menus at the top should now automatically show you "Tools for pivottable". Click the submenu "Alternativ" (for me it is already selected).

Now in the menu group "Data" you see "Change data source".

Regards
Bjorn