Share this content

Two pivot tables from one source


I am having difficulty with a pivot table issue. I have created one pivot from the source data that I am happy with, and do not wish to change.

The source data is brought into Excel via ODBC.

I am now trying to create a separate pivot from the same table, in the same workbook, but on a different page.

When I modify the second table, the layout of the first changes.

Where to I goI to 'unlink' the definitions?

I have tried creating fresh pivot tables from the same source sheet, but they still link.

Any assistance appreciated.

I know I could use separate workbooks, but would really like just the one workbook,


Please login or register to join the discussion.

23rd May 2012 18:59

In Excel 2007, yes if you refresh any of yout data, all pivot reports which are linked to the source are updated, but there are options to unlink in excel 2010 with power pivot.

Thanks (0)
By tom123
24th May 2012 07:18

Thanks - probably not a clear original post

Thanks Cantona1,

I now realise my post was not too clear. I get that the data itself refreshes in all pivot tables - which is fine,


it is more that if I try to (for example) have dates listed individually in one pivot table and then grouped into months in the second, that the table layouts seem to change in tandem.


I do like pivots, but when they don't do what you want they are very frustrating - interesting though..

Thanks (0)
By Ditta
24th May 2012 17:10

Pivot Table

I use multiple pivot tables from same source data in excel 2007.  I have never comes accross this issue.  I create a pivot table, copy and paste same pivot anywhere within same file and change the table layout (monthy revenue to weekly).  This does not change the settings in the 1st pivot table. 

which version of excel are you using?

There maybe an issue with source data format.



Thanks (0)
25th May 2012 12:59

If you are getting the data via ODBC

Why don't you generate the pivots directly from the source data?  Then there should, in Excel, be no link between them.

Thanks (0)
By tom123
28th May 2012 11:01

Here is the solution I found

Here is the neat solution I found elsewhere:

1) Create the two identical pivot tables in the same workbook

2) Cut and paste one of the tables to a new workbook (not just a new tab)

3) Alter the grouping on this table

4) Cut and paste the table back to the original worksheet.


I think this breaks the 'Pivot Table Cache' and enables the two pivots to function independently,


Thanks everyone

Thanks (0)