Slicers and SlicerCaches

By in Uncategorized on .

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.

12 thoughts on “Slicers and SlicerCaches

  1. Bob Phillips

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

  2. Antoinette Anderson

    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?

  3. Jeff Weir

    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?

  4. Antoinette Anderson

    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

  5. Jeff Weir

    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:

    1. Set up a ‘Template’ tab with the slicers and formatting that you want, but with all the data in it
    2. Copy that tab once for every manager, filter the table to show everything BUT the manager’s data, and delete those records Give the Tab the managers name
    3. Repeat steps 2 and 3 for every manager.

    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)>

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax