Reposted from http://answers.microsoft.com/en-us/office/forum/office_2010-excel/unlinking-copied-slicers-excel-2010-pivottables/7bd8b775-4595-4508-ac7b-1369673b3acd?tm=1411424412396 at the request of a forum moderator.

--------------------------------------------------------------------------------------------------------------------------------------

Ok, I'm probably missing something simple...

Excel 2010 x32 running on Win7x64

I have a pivottable connected to an OLAP cube, and added several slicers. I now need another copy of the pivot with mostly the same (but some different) info, and many of the same slicers, so I made a copy of the worksheet (right click on worksheet, duplicate).

The old and new worksheets have different names, and the pivottables have different names (changed on the pivottable ribbon). I also changed the slicer names and slicer captions.

So at this point, I would think that I could go under the slicer pivottable connections, and set the ones on the old sheet to the old pivot, and the ones on the new sheet to the new pivot... Instead, changing the connection for a slicer still changes the connection for the corresponding slicer on the other sheet. I change the slicerOld to pivotOld, then go to SlicerNew and change it to pivotNew, then go back to slicerOld and it is now set to pivotNew. Selections on either slicer are replicated to the corresponding slicer on the other page as well. Excel treats them as the same slicer, but I need them to be treated independently.

What property needs to be changed/unique between the original slicer and the copy, to allow them to independently accept the pivottable connection, and where is that property located? I even tried accessing the properties via the developer ribbon, to no avail.  I have quite a few slicers, and it would be a tremendous pain to recreate all my slicers for each report sheet I need to create (about 10 total)

Thanks!!

slicer is based on the pivottable as source. If just rename the pivottable, slicer is not be changed the source. so, you can't split. You might to copy both of pivottable and  slicer . see

http://www.sqlchick.com/entries/2011/11/27/comparing-slicers-in-excel-2010-to-standard-pivottable-filte.html

KR

Thanks for the reply

I had copied the whole sheet, so it copied the slicer(s) and corresponding pivottable. In the slicer settings (original and copy), I can see both pivot tables as independent tables. The problem is that the two slicers (original and copy) appear to be linked/mirrored and I haven't found a way to break the link; changing the pivottable connections on either one causes the same change on the other- basically, I can't point the original slicer to the original pivottable and the duplicate slicer to the copy of the pivottable, changing either slicer disrupts the pivottable connection of the other  :(

Hello Keith,

Thank you for your question.

I am trying to involve someone familiar with this topic to further look at this issue.

Regards,

George Zhao
Forum Support
________________________________________
Come back and mark the replies as answers if they help and unmark them if they provide no help.
If you have any feedback on our support, please click "tnfsl@microsoft.com"

Hi Keith,

Try modifying the data source of the copied PivotTable and test the issue.
I have tested the behavior on a small Pivot Table (not sourced through OLAP) and I have not seen the issue.

Regard,

Hi Keith,

There are two ways around:

1. First delete the slicers that are linked with the New Pivot that you want to be un-linked. Then, Create a replica of the Source (to keep the example simple, I would assume your Pivot's source is a Table). So, copy the sheet that has the SourceTable to a different sheet name. Then, rename the newly copied Table to something new. Then, on the Pivot that you want to un-link, change the source to the newly created source. At this point, you may continue to use the new source (whereas you can link the new source 1:1 with old), or you can switch the Pivot back to the original source, and delete the copied replica sheet/table. You can now add new Slicers to the Pivot and they should not be linked to the old Pivots.

2. Delete all slicers from everywhere on this workbook. Then, Add individual slicers to each sheet's Pivots. This will also ensure that each slicer gets connected to its own Pivot. Remember the trick, you have to first delete ALL slicers.

I hope this will solve your issue.

Thanks.

• Edited by Syed-H 44 minutes ago completely removed signature.

Hi Keith,

There are two ways around:

1. First delete the slicers that are linked with the New Pivot that you want to be un-linked. Then, Create a replica of the Source (to keep the example simple, I would assume your Pivot's source is a Table). So, copy the sheet that has the SourceTable to a different sheet name. Then, rename the newly copied Table to something new. Then, on the Pivot that you want to un-link, change the source to the newly created source. At this point, you may continue to use the new source (whereas you can link the new source 1:1 with old), or you can switch the Pivot back to the original source, and delete the copied replica sheet/table. You can now add new Slicers to the Pivot and they should not be linked to the old Pivots.

2. Delete all slicers from everywhere on this workbook. Then, Add individual slicers to each sheet's Pivots. This will also ensure that each slicer gets connected to its own Pivot. Remember the trick, you have to first delete ALL slicers.

I hope this will solve your issue.

Thanks.

• Edited by Syed-H Wednesday, September 02, 2015 6:47 AM completely removed signature.