Behind the scenes, Excel does quite a bit of smart rationalisation in terms of SlicerCaches whenever you connect Slicers to mulitple PivotFields, and this can be a bit confusing if you’re not familiar with what’s going on. So let’s take a look-see.
First, let’s create three PivotTables all based on the same data source – meaning they all share the same PivotCache and therefore can all be connected – or ‘daisy-chained’ – together with Slicers later on if we so desire:
Next, let’s add a separate Slicer for each of them, with each Slicer pointed at the “Item” field of it’s related Pivot:
So that’s what we see. How does Excel see this?
There’s a couple of points to note about this diagram. Firstly, the boxes across the top are screenshots from the Report Connections dialog box:
…which you get by right-clicking on a Slicer and selecting this:
<
And secondly, the reason I've drawn circles around those Item fields:
…is that I really want to underscore that slicers operate at the PivotField level, not on PivotTable level.
So where where we. Ah yes, three Pivots based on the same PivotCache, with three Slicers all pointing at the Item field of their respective PivotTable:
Let’s now change Slicer Two so that it also points at the Item field of PivotTable Three:
How did that change the conceptual lay of the land?
Well, that looks different. Excel rationalised the SlicerCaches by ditching SlicerCache three, and now both Slicer Two and Slicer Three are connected to SlicerCache Two. Meaning that conceptually, they both point at the Item field in both PivotTable2 and PivotTable3. In fact, if you were to right click Slicer Three and look at the ReportConnecitons, you’d see it looks exactly the same as for Slicer 2, even though we didn’t touch it. And if we change the selection in one of these Slicers, we see it replicated in the other as well as in each Pivot. It’s as if those Slicers are one and the same:
Interestingly, if we remove PivotTable3 from that SlicerConnections dialog:
…things don’t go back to the way before: Slicers Two and Three are still synced together, but control PivotTable2 only. PivotTable3 is completely slicer-less:
There’s no way you can get that Slicer Three to operate independently on its own again. You’ll just have to delete it and add another, I’m afraid.
Adding Slicers Programatically
If you record a macro while adding a slicer, you get code like this:
ActiveWorkbook.SlicerCaches.Add(ActiveSheet.PivotTables(“PivotTable1”), “SomePivotField”). _
Slicers.Add ActiveSheet, , “SomePivotField”, “SomePivotField”, 146.25, 309.75, 144, 187.5
All those arguments of the Slicers.Add command are optional except the first. And all those numbers just tell Excel where you want the Slicer, and how big you want it to be. So you could just go ahead and use this for the same result:
ActiveWorkbook.SlicerCaches.Add(ActiveSheet.PivotTables(“PivotTable1”), “SomePivotField”). _
Slicers.Add ActiveSheet
You can actually add a SlicerCache that controls a pivot without adding a Slicer:
ActiveWorkbook.SlicerCaches.Add ActiveSheet.PivotTables("PivotTable1"), "SomePivotField"
…and then you can connect another PivotTable to it:
ActiveWorkbook.SlicerCaches("Slicer_SomePivotField").PivotTables.AddPivotTable (ActiveSheet _
.PivotTables("PivotTable2"))
…meaning you now have an invisible slicer that keeps the pivots in sync, based on user selections from the pivot filters themselves. Spooky! Note that –
- If you add another slicer to that same PivotField on either of those PivotTables, Excel simply uses the slicer cache you just set up, meaning the new slicer controls BOTH pivots, even though you just added it to one.
- If you delete that slicer, Excel performs a Slicer Exorcism: it deletes the underlying SlicerCache, meaning your two pivots are no longer synced. Unspooky!
Here’s something else slightly spooky. Or rather, kooky. Normally if you delete a pivot that is the ONLY pivot that uses a particular PivotCache, Excel gets rid of the PivotCache automatically. Excel basically thinks “Well, we won’t need that crap lying around anymore”. But strangely, if you have a slicer set up for that pivot, then deleting the pivot leaves both the slicer AND the pivot cache alone. The PivotCache only gets deleted once you delete that orphaned slicer.
Well, that’s enough for today. But *HORROR* there’s some Slicer-related sequels coming to a screen near you soon:
So stay tuned. And awake.
Hi Jeff,
Did I overlook the sample file you posted to illustrate you point(s) ?
No…I didn’t think one was warranted.
I wouldn’t object if you posted it.
I wrote some stuff about slicers too: http://www.jkp-ads.com/articles/slicers00.asp
Yeah, but you didn’t do a movie poster…
;-)
The fact that deleting the pivot doesn’t delete the slicer/slicercache is very serendipitous when using cube formulae.
True! A silver lining indeed.
Hello
I may have lost 99.99% of my sanity trying to figure out a vba code for my slice and dice needs and praying you can help :( and I am a newbie!
I have a sheet that downloads financial data from our database, I have set up a macro to filter each manager’s transactions to their own sheets, and to transform, subtotal and add variance analysis to these. My code also converts each manager’s data to a table, and each sheet and table carries the manager’s name.
Now I would super duper love to add slicers to each managers sheet linked to their own table, so each manager would have the same slicer sets but to control their own individual table independently.
Is their anyway that I can dynamically reference the slicer cache to look at the manager’s table as it loops through all the manager sheets? Or am I asking the impossible?
Are we talking about table slicers here? And can you elaborate further on what you mean by “the slicer cache”? Are you wanting to filter all tables so that initially they show the same view, but still let individual managers change that view?
Hi Jeff, thanks for responding!
Yes these would be table slicers.
So to elaborate – my workbook starts with 3 sheets being Input, Actual Data, Budgets
– The Actual Data downloads from the SQL database, transaction by transaction, for each manager.
– The macro filters each manager’s transactions on the Actual Data sheet, creates a new sheet (named after the manager), copies over the transaction data for that manager, subtotals it and turns the range into a table (named after the manager).
– The above macro is set up to loop through all the sheets to other that the initial 3 (so every sheet named after a manager) to do the subtotal, formulas and formatting.
– Managers may change over time.
– So I end up with x number of tables in the workbook dependent on how many managers there are when running the macro, and the name of each table depends on the name indicated in an index for the position at the time of running the macro. i.e. I cannot set a code up on fixed references.
– Now I would like to extend my code, that once the range of the current manager is converted into a table, it inserts slicers linked to the active manager’s table (just created). And then the full macro will loop through on the next manager’s sheet and inserts slicers to the next manager’s table.
– So if we have manager “A”, “B” and “C”, we will have a sheet named “A”, “B” and “C”, and we will have a tables named “A”, “B” and “C”. And I would like each table to have slicers “Period”, “Description”, “Location” (no filters applied to any slicers yet). When Manager A opens the workbook and goes to sheet “A” he will see table “A” and slicers “Period”, “Description”, “Location”. He must then be able to filter as he needs to and have the filtering only happening on table “A”, not any others. Same for “B” and “C”.
– Do I have to create a code outside of the loop for each sheet? Or is there a way to code the slicers into the loop driven by the table name?
– Hence I need a dynamic Slicer Cache reference based on the table name of the active manager in the loop? (I think that is how to phrase what I need?)
I hope the above makes more sense :) if seeing my workbook as it looks currently helps then I can arrange to be uploaded.
Many thanks,
Antoinette
Okay. This article applies to PivotTable Slicers. They are a very different beast than Table Slicers. Tables slicers can only filter one Table. So when you say He must then be able to filter as he needs to and have the filtering only happening on table “A”, not any others. then that’s the behavior you have right out of the box. Rather than looping through and adjusting formatting/adding slicers, I would do this:
If the programming is beyond your current capabilities, I’d suggest you either post a question on a help forum (but just one help forum), or check out Mike Alexander’s Data Exlpode addin at http://www.datapigtechnologies.com/ExcelExplosion.htm (although I’m not sure if Mike’s addin will let you create copies of everything in the sheet, or just the Table. Flick him an email at the address on that page…I’m sure he’d happily change his addin to accomodate if he thought the requirement was a common one)>
You are awesome Jeff! I will give that a go :)
Thanks a mil xoxo