Slicers and SlicerCaches

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:
 
Three Pivots One Cache

Next, let’s add a separate Slicer for each of them, with each Slicer pointed at the “Item” field of it’s related Pivot:
 
Slicers and Pivots only
 
 
So that’s what we see. How does Excel see this?

Seperate SlicerCaches

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:
 
ReportConnections

…which you get by right-clicking on a Slicer and selecting this:
 
ReportConnections<

And secondly, the reason I've drawn circles around those Item fields:
 
PivotField level

…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:
 
Seperate SlicerCaches

Let’s now change Slicer Two so that it also points at the Item field of PivotTable Three:
 
ReportConnections - both
 
How did that change the conceptual lay of the land?
Shared SlicerCaches2

 
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:
One and the same
 
Interestingly, if we remove PivotTable3 from that SlicerConnections dialog:
 
ReportConnections - remove 3
 
…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:
 
Remove PT3  from Slicer Two

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:
 
Nightmare

So stay tuned. And awake.

7 Comments

  1. snb says:

    Hi Jeff,

    Did I overlook the sample file you posted to illustrate you point(s) ?

  2. Jeff Weir says:

    No…I didn’t think one was warranted.

  3. snb says:

    I wouldn’t object if you posted it.

  4. Jeff Weir says:

    Yeah, but you didn’t do a movie poster…
    ;-)

  5. Bob Phillips says:

    The fact that deleting the pivot doesn’t delete the slicer/slicercache is very serendipitous when using cube formulae.

  6. Jeff Weir says:

    True! A silver lining indeed.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: