Pivottable Slicers

Introduction

Pivottables are one of the most powerful data analysis features Excel has to offer. From version to version, Microsoft have added functionality to this feature. Pivottable Slicers were introduced with Excel 2010 and as of Excel 2013 you can also insert a slicer to filter a table. In this article I'll discuss how you can put slicers to work.

Content

Why Slicers

If you've ever done filtering on (Pivot) tables before you'll know why this can be a challenge. But I'll spell it out here in case you haven't.

With any version of Excel, you can filter the content of your table or Pivottable report (or pivot chart for that matter) by clicking the appropriate dropdown and checking/unchecking boxes:

Filtering a Pivottable field
Old-style (Pivot) table filtering

This method has a number of drawbacks listed below, in no particular order:

Slicers put an end to a number of these problems:

Filtering a Pivottable field with slicers
Slicer filtering

Configuring Slicers

Slicer options on the ribbon

There is a host of settings you can apply to your slicers. The Ribbon shows most of them:

Excel 2010 ribbon for slicer editing
Ribbon (cut in two halves so it fits on this page) showing the slicer options tab.

The Slicer Tools contextual ribbon tab houses a number of groups to change your slicer's appearance and behavior. Let me discuss them here.

Slicer group

This group houses three important controls. You can change the name of a slicer here (Slicer Caption), change some settings controlling which items are listed in a slicer and how they are sorted and filtered (Slicer Settings). Also, you can set up which Pivottables connect to your slicer (I explain that below).

Slicer Styles Group

A quick way to format your slicers is by applying one of the built-in slicer styles. Like any style in Excel 2010, these adhere to the theme you've selected. This makes it easy to keep your workbook design tidy and consistent.

Like cell styles you can create your own slicer styles too, by right-clicking one of them and choosing "Duplicate", followed by right-clicking the newly duplicated style and choosing "Modify".

Buttons group

Just like you can easily change the colors of your slicers by applying a different style to the slicer it is simple to modify the button layout and appearance, just adjust the numbers in that group of the ribbon.

Buttons group
These three controls enable you to change the button layout and size

Size group

And last but not least a small group which lets you edit the button sizes.

Commands not in ribbon

Unfortunately, not everything you need is on the ribbon. The missing options are quickly accessible through the slicer's right-click menu however. Select the "Size and Properties" option to get there. Here you can control whether or not the slicer must be printed and should move/size with cells just like any other shape on a sheet. And you can turn off resizing and moving to prevent your users from messing with your layout.

Changing a Slicer's font size

If you've scurried through all slicer dialogs like I have, you'll have noticed there is no way to change the font size of a slicer.

Or is there? Of course there is, but is has been cunningly hidden!

The basic idea is that you must edit the slicer's style. But since you can only edit custom styles, the first step is to add a custom style. Pick one you like best and right-click it, then select Duplicate...:

Right-click a slicer style
Right-click a slicer style and select "Duplicate"

The second step is editing the new custom style. Right-click that style and select "Modify...":

Right-click a slicer style
Right-click a slicer style and select "Modify".

In the dialog that pops up, select "Whole Slicer" end then click the Format button:

The Modify slicer Quick style dialog
The Modify slicer Quick style dialog.

As you can see in the screenshot below, now you can edit the slicer's Font.

The Format Slicer Element dialog
Time to select a nicer font for your slicer!.

After you OK this dialog, probably nothing appears to happen to your slicer. Don't worry, all that is needed now is applying your new custom style to the slicer and you're done!

A very useful option of slicers is that you can tie them to more than one Pivottable and/or -chart.

The way to handle this is by selecting the slicer in question and then clicking the Pivottable Connections button in the Slicer Settings group on the Slicer Tools contextual ribbon tab:

the Pivottable Connections button
the Pivottable Connections button in the Slicer Settings group on the Slicer Tools contextual ribbon tab.

This brings up the following tiny dialog:

the Pivottable Connections dialog
the Pivottable Connections dialog.

As you can see, Excel has listed a couple of Pivottables in your file to tie the slicer to.

Luckily, Excel is smart enough to ensure that only Pivottables using the same cache as the selected slicer is tied to are in the list.

If you have more than one slicer on a particular field (e.g. a slicer on "City" on each sheet with a Pivottable), then you must take care when selecting which tables the slicer relates to. As soon as you check more than one check box, the slicers working on the checked Pivottables will be permanently tied together. The only way to make the slicers work independently again is by using the Undo button.

Slicers and VBA

This chapter has also been published on The Microsoft Office Blog.

Juggling the SlicerCaches collection

For each slicer you add to your workbook, Excel adds a SlicerCache object too, which controls which Pivottable(s) your slicer controls.

So suppose we have a workbook with two Pivot caches. Pivotchache1 has two pivot tables: Pivottable1 and Pivottable2. As soon as you add a slicer to a Pivottable (even if the slicer ties to the same field of the Pivottable) you get a SlicerCache object tied to the pivotCache to which the pivot table belongs:

Hierarchy of the slicer and it's family
Hierarchy of the slicer and its family

Note that the above picture is not entirely correct. In the object model, PivotTables are in fact tied to a slicerCache, not to a slicer. But on the user interface side of things it is the slicer the user actually sees, which is why I put that in-between.

Also note that this is a diagram for pivot table slicers. A table slicer has a much simpler diagram where a table has one slicer cache with attached to that all slicers pertaining to that table. No other tables are involved.

The code below enumerates all slicer caches and their associated pivot tables in your workbook:

Sub MultiplePivotSlicerCaches()
    Dim oSlicer As Slicer
    Dim oSlicercache As SlicerCache
    Dim oPT As PivotTable
    Dim oSh As Worksheet
    For Each oSlicercache In ThisWorkbook.SlicerCaches
        For Each oPT In oSlicercache.PivotTables
            oPT.Parent.Activate
            MsgBox oSlicercache.Name & "," & oPT.Parent.Name
        Next
    Next
End Sub

As soon as you check more than one Pivottable on the Pivottable Connections dialog of a Slicer, the slicers in question will share a single SlicerCache object. The other SlicerCache object will be removed from the collection. This explains why you cannot go back by unchecking all but one Pivottable in that dialog: all joined slicers will now be changed by changing the checked Pivottable(s) on any of them. In turn, each checked Pivottable becomes part of the Pivottables collection of the remaining SlicerCache object.

If you decide to select Slicer1 and change its pivot connections by checking both Pivottable1 and Pivottable2, one slicer cache is deleted (the one belonging to the Pivottable you checked to add to the current slicer). So the hierarchy changes to:

Changed hierarchy of slicers
Changed hierarchy of slicers

So both Slicer1 and Slicer2 control Pivottables 1 and 2. The slicers 1 and 2 are in sync too because it is in fact the SlicerCache that is changed by the slicer. So the hierarchy in the picture above isn't entirely true.

Changing the buttons using VBA

It is easy enough to change the button appearance using a bit of VBA:

Sub AdjustSlicerButtonDimensions()
    With ActiveWorkbook.SlicerCaches("Slicer_City2").Slicers("City 2")
        .NumberOfColumns = 3
        .RowHeight = 13
        .ColumnWidth = 70
         'Note that changing the ColumnWidth also affects the Width of the slicer itself
         'So the next line will change the ColumnWidth!
        .Width = 300
     End With
End Sub

Note that the numbers do not coincide with what is shown on the ribbon. Apparently the unit of measure differs between VBA and the ribbon.

Changing the slicer itself and some SlicerCache properties

Changing some aspects of your slicer using VBA isn't hard to do. In fact, the macro recorder makes finding out how this works relatively simple. After changing some settings and doing a bit of tidying up I got:

Sub AdjustSlicerSettings()
    With ActiveWorkbook.SlicerCaches("Slicer_City2").Slicers("City 2")
        .Caption = "City"
        .DisplayHeader = True
        .Name = "City 2"
    End With
    With ActiveWorkbook.SlicerCaches("Slicer_City2")
        .CrossFilterType = xlSlicerNoCrossFilter
        'xlSlicerCrossFilterShowItemsWithDataAtTop:
        '   Visually indicate items with no data, items with data are pushed to the top
        'xlSlicerCrossFilterShowItemsWithNoData:
        '   Visually indicate items with no data, items with no data stay put
        'xlSlicerNoCrossFilter:
        '   No indication for items with no data.
        .SortItems = xlSlicerSortAscending
        .SortUsingCustomLists = False
        .ShowAllItems = False
        '   This ensures that data no longer in the pivot cache is not shown on the slicer
    End With
End Sub

Synchronising Slicers

Oftentimes a reporting workbook contains more than one pivot table. As I showed you already, synchronising pivot tables that use the same pivot cache is very easy if you tie them together with one or more slicers pointing to both pivot tables. You use the "Pivottable Connections" button on the ribbon for that.

However, it frequently happens that pivot tables do not share a pivot cache, but still need to be in sync, for instance if your slicer is filtering on a reporting period such as Year, Quarter or Month. In the code below I assume all pivot caches share the fieldnames to filter on. I also assume the tied-together slicer caches share the same values, in other words, all years appearing in Slicer_Year also appear in Slicer_Year1 and vice versa. If your situation differs (values in slicer cache 1 might be missing in slicer cache 2 or the other way around), you will need to take special precautions.

Setting things up

The first action needed is to make sure each slicer is connected to all relevant pivot tables belonging to its pivot cache. Take care NOT to add a pivot table to a slicer for which the field in question is not used in that pivot table, because a slicer can operate on a field that isn't included in the pivot table itself. It will filter your pivot table and the pivot itself gives no clue that it was filtered (other than the slicer).

Suppose you have three reports: Monthly, Quartely and Yearly. You might have three slicers for those pivot tables: Year, Month and Quarter. But for the quarterly and yearly pivot table, it makes no sense to tie them to the Month slicer. In fact, it would turn them all into a monthly report. In a situation like this, only the Year slicer is a sensible one to connect to all three pivot tables. If you would want to connect the Quarter slicer to the Month slicer, you would need some VBA which, after selecting a month, sets the associated Quarter in the Quarter slicer. The code below isn't that smart, it just syncs Year, Quarter and Month slicers, assuming you did all of the slicers pivot table connections correctly.

Triggering a change in a slicer filter

There is no event tied to slicer actions directly. However, clicking a slicer triggers the Workbook_SheetPivotTableUpdate event in the ThisWorkbook module (there is a similar event in the sheet modules, but I want one generic event which responds to any pivot table update). The empty event stub looks like this:

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)

End Sub

Sh is an object variable pointing to the worksheet on which a pivot table just updated. Target is an object variable pointing to the pivot table that just updated.

The first thing the code needs to do is find out which slicers are connected to the pivot table that just changed and set a pointer to the ones we want to use to sync the other pivot tables to.

First, we declare some variables:

    Dim oScMonth As SlicerCache
    Dim oScKwrt As SlicerCache
    Dim oScYear As SlicerCache
    Dim oSc As SlicerCache
    Dim oPT As PivotTable
    Dim oSi As SlicerItem

The first three are going to hold the Year, Quarter and Month slicers tied to the changed pivot table. Note that no more than two of these three are in fact tied to the pivot table: either Year and Month, Year and Quarter or just Year.

Finding the slicer that was clicked

So now step through all SlicerCaches and then through all their associated pivot tables to find out which slicers are tied to the changed pivot table:

    For Each oSc In ThisWorkbook.SlicerCaches
        For Each oPT In oSc.PivotTables
            If oPT.Name = Target.Name And oPT.Parent.Name = Target.Parent.Name Then
                If oSc.Name Like "*Year*" Then
                    Set oScYear = oSc
                ElseIf oSc.Name Like "*Month*" Then
                    Set oScMonth = oSc
                ElseIf oSc.Name Like "*Quarter*" Then
                    Set oScKwrt = oSc
                End If
                Exit For
            End If
        Next
        If Not oScYear Is Nothing And Not oScMonth Is Nothing And Not oScKwrt Is Nothing Then Exit For
    Next

As you can see, we only pick up the slicers Month, Quarter and Year because those are the ones we want to sync.

Synchronising the slicers

Now that we have the slicer(s) that have been clicked, we want to sync them with slicers with similar names. By Default, Excel names SlicerCaches like this: Slicer_FieldName. If another slicer with the same fieldname is added (which adds a SlicerCache too), it is called Slicer_FieldName1, the next one is called Slicer_FieldName2 and etcetera. So what the code below does is loop through all slicer caches again, looking at their names to figure out which ones belong together.

The code has a special trick to avoid problems. Suppose you have a slicer which has its first item selected and the remaining items de-selected. Suppose that the sister slicer is clicked and we click the second item. When the code runs through all slicer items, it will start with the first one, which will be de-selected. This causes the slicer to select all items, because it is not allowed to de-select all items of a slicer. Subsequently the code runs through the remaining items and sets them as needed. The end result is that both item 1 and item 2 are selected, rather than just item 2. The trick around this is setting the last slicer item to "Selected" before running through the loop:

    If Not oScYear Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScYear.Name, 7, 3) And oSc.Name <> oScYear.Name Then
                'This one has a similar fieldname (first three characters are compared in this case)
                'but not the same name, as that would be the same slicer cache.
                'So synch it with the changed year slicer
                'If a slicer has the very first item selected and you subsequently de-select it,
                'the end result is that all sliceritems get selected. So select the last item of the slicer first
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScYear.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If

Putting it all together

So if you put all of this together, this is what you end up with:

'Variable to prevent event looping:
Dim mbNoEvent As Boolean

Private
Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oScMonth As SlicerCache
    Dim oScKwrt As SlicerCache
    Dim oScYear As SlicerCache
    Dim oSc As SlicerCache
    Dim oPT As PivotTable
    Dim oSi As SlicerItem
    Dim sYear As String
    Dim bUpdate As Boolean
    'Prevent event looping, changing a slicer in this routine also triggers this routine
    If mbNoEvent Then Exit Sub
    mbNoEvent = True
    bUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    For Each oSc In ThisWorkbook.SlicerCaches
        For Each oPT In oSc.PivotTables
            If oPT.Name = Target.Name And oPT.Parent.Name = Target.Parent.Name Then
                If oSc.Name Like "*Year*" Then
                    Set oScYear = oSc
                ElseIf oSc.Name Like "*Month*" Then
                    Set oScMonth = oSc
                ElseIf oSc.Name Like "*Quarter*" Then
                    Set oScKwrt = oSc
                End If
                Exit For
            End If
        Next
        If Not oScYear Is Nothing And Not oScMonth Is Nothing And Not oScKwrt Is Nothing Then Exit For
    Next
    If Not oScYear Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScYear.Name, 7, 3) And oSc.Name <> oScYear.Name Then
                'This one has a similar fieldname (first three characters are compared in this case)
                'but not the same name, as that would be the same slicer cache.
                'So synch it with the changed year slicer
                'If a slicer has the very first item selected and you subsequently de-select it,
                'the end result is that all sliceritems get selected. So select the last item of the slicer first
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScYear.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If
    If Not oScKwrt Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScKwrt.Name, 7, 3) And oSc.Name <> oScKwrt.Name Then
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScKwrt.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If
    If Not oScMonth Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScMonth.Name, 7, 3) And oSc.Name <> oScMonth.Name Then
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScMonth.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If
    mbNoEvent = False
    Application.ScreenUpdating = bUpdate
End Sub

Show Selected Items

Even though it is often sufficient to see the slicer on the workbook it can be useful to be able to get a list of the filtered sliceritems in a worksheet cell. If your pivot table is built using a Power Pivot model, then you can access this information by using the CUBERANKEDMEMBER worksheet function. If however the slicer was built off of a "normal" pivot table, then you need a bit of VBA code in a User Defined Function (UDF).

The slicer name

In order to retrieve the selected slicer items we need slicer's internal name, both for the CUBERANKEDMEMBER function and for the UDF. This name can be shown in the user interface by right-clicking the slicer in question and selecting "Slicer settings". The name you are looking for is marked in red in the screenshot below and indicated by "Name to use in formulas":

Slicer settings dialog, needed name highlighted in red

CUBERANKEDMEMBER (for Power Pivot slicers)

The CUBERANKEDMEMBER function returns an array of selected items and hence needs to be into as many (vertical) cells as you expect will be selected in the slicer. See the screenshot below.

Using the CUBERANKEDMEMBER function

The formula is:

=CUBERANKEDMEMBER("ThisWorkbookDatamodel",Slicer_ShipCountry,SEQUENCE(CUBESETCOUNT(Slicer_ShipCountry)))

Note, that this is a dynamic array formula that only works in Excel 365. For older versions you should use a formula like the one below and copy that formula down far enough:

=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",Slicer_Name,ROW()-ROW($G$2)),"")

The UDF

The code sample below must be placed in a normal module:

Public Function GetSelectedSlicerItems(SlicerName As String) As String
    Dim oSc As SlicerCache
    Dim oSi As SlicerItem
    Dim lCt As Long
    On Error Resume Next
    Application.Volatile
    Set oSc = ThisWorkbook.SlicerCaches(SlicerName)
    If Not oSc Is Nothing Then
        For Each oSi In oSc.SlicerItems
            If oSi.Selected Then
                GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
                lCt = lCt + 1
            End If
        Next
        If Len(GetSelectedSlicerItems) > 0 Then
            If lCt = oSc.SlicerItems.Count Then
                GetSelectedSlicerItems = "All Items"
            Else
                GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)
            End If
        Else
            GetSelectedSlicerItems = "No items selected"
        End If
    Else
        GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
    End If
End Function

The function takes the slicer name as its argument and returns a text string (comma delimited) containing the selected items. In a worksheet cell the formula would look like this:

=GetSelectedSlicerItems("Slicer_TeamID2")

Conclusion

Well, I hope I got you started with getting your head around how slicers work and how to address them using VBA. I find Slicers a real gem in Excel. A great addition to the product!

Sample files

Download sample Excel workbooks (29 Aug 2019, downloaded 5.607 times)

Other sources

Microsoft Excel - Easy (and Even Fun!) Data Exploration: Introducing Excel 2010 Slicers

Microsoft Excel - Interacting with Slicers

Wall Street Oasis - Slicer in Excel: Tool Guide - Images and Video Instructions in Excel


Comments

All comments about this page:


Comment by: General Ledger (12-1-2011 19:22:29) deeplink to this comment

WOW!! How did you discover all this great stuff? Thanks for revealing these secrets. Please keep this information coming.

GL


Comment by: Vishwanath Nayak (17-1-2011 23:22:11) deeplink to this comment

Dear, I recently purchased a new Dell optiplex 380 with a MS excel non commercial use,, but i dont see this Slicers ribbon/options, tools in my excel while playing with pivots....How should i debug this ?

Regards,
Vn


Comment by: Jan Karel Pieterse (18-1-2011 01:04:14) deeplink to this comment

Hi Vishwanath,

What Office version are you using? SLicers were introduced with Excel 2010 (=Office 2010) and are unavailable in Excel 2007 and older.

If you do have 2010, make sure you select a cell inside a pivot table so that the Pivot tools tab is visible on the ribbon. The slicer button is on that tab.


Comment by: Rick Freitas (9-2-2011 16:28:04) deeplink to this comment

Hi Jan,

I noticed in the slicer settings box there is a "Use Custom Lists when sorting" check box. Do you know how you call a custom list to sort items in slicer box? Assuming probably VBA code to do that but haven't been able to find any examples. Thanks for your help with this.

Regards,

Rick


Comment by: Jan Karel Pieterse (10-2-2011 05:30:12) deeplink to this comment

Hi Rick,

I assume this works exactly the same as "normal" custom lists are used when Excel does sorting. I'd look up custom lists in Excel help.


Comment by: Pradeep (10-2-2011 15:51:34) deeplink to this comment

Hi,
I am using excel 2010 but can not find slicer option on ribbon. Evenif i select a cell in piot table it doesnot show slicer option in pivot table tab.

Do i have to download it as and addin.

Pradeep


Comment by: Jan Karel Pieterse (11-2-2011 03:59:08) deeplink to this comment

Hi Pradeep,

It is located in the Sort & FIlter group, on the Options tab of the Pivottable Tools contextual tab of the ribbon. Note that this option is disabled if you are in a pivot table in a file that was saved in Excel 97/2003 format. It has to be a "normal" Excel 2007/2010 format workbook.


Comment by: Guy Brown (3-3-2011 09:52:15) deeplink to this comment

Slicers are the best part of Excel 2010 - especially the ease with which one can connect multiple pivot tables with a single slicer.
Unfortunately there appears to be not backward compatibility with Excel 2007 at all (I'm surprised Microsoft have not come up with a "compatibility pack") - even in xlsm format.
Is there a way of maintaining the pivot connections when opening a "slicered" 2010 workbook in 2007? (though in 2007 I know the old style "check box" filters would have to be used)


Comment by: Jan Karel Pieterse (4-3-2011 02:03:47) deeplink to this comment

Hi Guy,

Not sure what is supposed to happen. The information I had so far was that 2010 objects are ignored by Excel 2007, but still included in a save. So I was expecting them to carry over unscathed. Are you saying they don't?
If so, it may be useful to send me the workbook (the version before 2007 and after 2007 has touched it) so I can issue a bug report.


Comment by: Jason (29-4-2011 07:42:54) deeplink to this comment

I think the question may be slightly misrepresenting the issue. If you have already created a slicer, the Slicers ribbon/options, tools is a contextual tab/ribbon option that shows when you select the slicer itself. It will show up as the right most ribbon option when the slicer is selected.


Comment by: jason (29-4-2011 07:43:50) deeplink to this comment

I'm interested to know what "hovered" means in the slicer element section of of the above image.

Thanks


Comment by: Jan Karel Pieterse (1-5-2011 21:25:50) deeplink to this comment

Hi Jason,

Hovered means the format the slicer item gets when the mouse hovers over it.


Comment by: Jason Parrish (2-5-2011 05:48:16) deeplink to this comment

Thanks Jan.


Comment by: Sebastien (18-5-2011 05:50:45) deeplink to this comment

Hi,
I'm trying to get the selected items from a slicer using VBA.
I can't find how to use:

ActiveWorkbook.SlicerCaches("Slicer_xxxx").VisibleSlicerItems
or
VisibleSlicerItemsList
.
I am able to select items in a slicer, filling up the VisibleSlicerItemsList, but not get what is selected.
Do you have any ideas?


Comment by: Jan Karel Pieterse (18-5-2011 06:17:43) deeplink to this comment

Hi Sebastien,

Have a look at this little macro, maybe it gets you going:

Sub GetSlicerValues()
    Dim oSi As SlicerItem
    For Each oSi In ActiveWorkbook.SlicerCaches("Slicer_City").SlicerItems
        MsgBox "Slicer value: " & oSi.Value & ", Selected: " & oSi.Selected & ", Available: " & oSi.HasData
    Next
End Sub


Comment by: Sebastien (19-5-2011 01:02:40) deeplink to this comment

I tried what you explained but I get "Run-time error '1004': Application-defined or object-defined error" because of SlicerItems.

Do you know how to fix that?


Comment by: Jan Karel Pieterse (19-5-2011 02:44:56) deeplink to this comment

Which line is highlighted if you press debug?

If it is the msgbox line, hover your mouse over each part of that statement, which part shows the error message in the tooltip?


Comment by: Sebastien (19-5-2011 03:28:41) deeplink to this comment

The error comes on the line:
For Each oSi In ActiveWorkbook.SlicerCaches("Slicer_City").SlicerItems

I tried to do
Dim oSc As SlicerCache
Set oSc = ActiveWorkbook.SlicerCaches("Slicer_City")
For Each oSi In oSc.SlicerItems
...

And the error comes on the For Each line.
That's why I think the problem comes from SlicerItems.


Comment by: Jan Karel Pieterse (19-5-2011 06:37:48) deeplink to this comment

Hi Sebastien,

Odd, as it works flawlessly for me. Does the slicer actually have any items visible?


Comment by: Sebastien (19-5-2011 06:48:17) deeplink to this comment

Yes, I tried with different slicers with some selected items or not.
I don't really understand this error.
Could it be a security issue or something like that?


Comment by: Jan Karel Pieterse (19-5-2011 07:40:28) deeplink to this comment

Hi Sebastien,

Send me the file in question and I'll have a quick look.

See email address near bottom of this page.


Comment by: Jan Karel Pieterse (22-5-2011 21:52:40) deeplink to this comment

Hi Sebastien,

Found the problem. If your Pivot table is getting data from an external source, you must use the SlicerCacheLevels collection to find out what items are in the slicer:

Sub GetSlicerValues()
    Dim oSi As SlicerItem
    Dim oSl As SlicerCacheLevel
    For Each oSl In ActiveWorkbook.SlicerCaches("Segment_Date").SlicerCacheLevels
        For Each oSi In oSl.SlicerItems
            MsgBox "Slicer name: " & oSl.Name & ", Slicer value: " & oSi.Value & ", Selected: " & oSi.Selected & ", Available: " & oSi.HasData
        Next
    Next
End Sub


Comment by: Andrew Fedor (2-6-2011 19:48:12) deeplink to this comment

I have 2 pivot charts in the same dashboard that are from 2 different datasets. In other words the tables that the pivot tables were bulit from are different. Is there a way to a single slicer to control both pivot charts? One chart is sales and the other is backlog and I want to look at both by salesman. So when I am looking salesman#1, I want to see sales for salesman#1 and backlog for the same salesman.


Comment by: Jan Karel Pieterse (5-6-2011 23:55:44) deeplink to this comment

Hi Andrew,

As far as I know, Each pivot cache has its own set of slicers, so the short answer is no.
But you could use some VBA code to synch two slicers.

For example, this code -placed in the ThisWorkbook module- will synchronize two slicer caches. The code only responds to a change in a slicer named Slicer_City, located on Sheet2 and will update Slicer_City2 located anywhere:

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oSl As SlicerItem
    If Sh.Name = "Sheet2" Then
        With SlicerCaches("Slicer_City")
            For Each oSl In .SlicerItems
                SlicerCaches("Slicer_City2").SlicerItems(oSl.Caption).Selected = oSl.Selected
            Next
        End With
    End If
End Sub


Comment by: Andrew Fedor (6-6-2011 13:23:04) deeplink to this comment

Jan:

Thank you for your assistance. I replaced your sheet and slicer names with mine from my workbook, but when I execute the code I get an error on line 6 "SlicerCaches("Slicer_City2....."

The error is -- Method 'Selected' of object 'SlicerItem' failed.

I checked the spelling of my sheet and slicer names and they are correct.

Any ideas.


Comment by: Jan Karel Pieterse (7-6-2011 00:15:29) deeplink to this comment

Hi Andrew,

If you email me your workbook I'll try to find a bit of time to look at it. Please add which slicers you want to work together (where they are located in the workbook)


Comment by: Ian Tibot (6-7-2011 05:35:03) deeplink to this comment

Hi Jan,

I have the same issue as Andrew, my data is coming from an external source but from two different queries that have common elements. If you have found a way to effectively make a second slicer a slave to a 'master' slicer that would solve my problem.

I've tried to use the code that you proposed for Andrew but when i go to run the macro it opens the run macro box and i have nothing to run.

Cheers

Ian


Comment by: Jan Karel Pieterse (6-7-2011 06:49:44) deeplink to this comment

Hi Ian,

Try if code like this does any better.
Note that this code should fire off automatically once you change a slicer.
It is up to you to adapt the code to your slicer names and such!

Dim mbNoEvents As Boolean

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oSl As SlicerItem
    If mbNoEvents Then Exit Sub
    'IN case a slicer item in slicer 1 is not present in slicer 2
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    If Sh.Name = "Charts" Then
        mbNoEvents = True
        With SlicerCaches("Slicer_Salesman")
            For Each oSl In .SlicerItems
                If SlicerCaches("Slicer_Salesman2").SlicerItems(oSl.Caption).Selected _
                 <> oSl.Selected Then
                    SlicerCaches("Slicer_Salesman2").SlicerItems(oSl.Caption).Selected _
                        = oSl.Selected
                End If
            Next
        End With
    End If
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    mbNoEvents = False
End Sub


Comment by: Jan Karel Pieterse (6-7-2011 06:50:33) deeplink to this comment

Hi Ian,

NB: The code goes into the ThisWorkbook module!


Comment by: Shadi Al. Kurdi (27-7-2011 23:11:36) deeplink to this comment

Hi Jan,

is there any possibility to do Custom sorting in Slicer items by VBA code?

The only options available are A-Z or Z-A

BR
Shadi


Comment by: Jan Karel Pieterse (22-8-2011 03:01:54) deeplink to this comment

Hi Shadi,

I have not tested this, but you might try adding a custom sort list that is in the order you want and then do the sort? Sort lists can be accessed through File, Options, Advanced tab, General category, Edit custom lists option.


Comment by: Trisha (27-8-2011 09:23:46) deeplink to this comment

My slicer function is missing also. My spreadsheet is saved in .xlsx version, but still missing. Any suggestions?


Comment by: Jan Karel Pieterse (29-8-2011 01:32:27) deeplink to this comment

Hi Trisha,

If you add a new pivot table to the xlsx file, does that new Pivot table allow inserting of a slicer?


Comment by: RealParadox (29-10-2011 08:13:06) deeplink to this comment

I have problem with osi.selected = false

Sub GetSlicerValues()
    Dim oSi As SlicerItem
    Dim oSl As SlicerCacheLevel
    For Each oSl In ThisWorkbook.SlicerCaches("Slicer_Year").SlicerCacheLevels
        For Each oSi In oSl.SlicerItems
        ''MsgBox "Slicer name: " & oSl.Name & ", Slicer value: " & oSi.Value & ", Selected: " & oSi.Selected & ", Available: " & oSi.HasData
        If oSi.Value = "2011" Then
         MsgBox "Yes"
         oSi.Selected = True
        Else
            MsgBox "No"
        End If
         '' MsgBox "Slicer name: " & oSl.Name & ", Slicer value: " & oSi.Value & ", Selected: " & oSi.Selected & ", Available: " & oSi.HasData
        Next
    Next
End Sub

maybe someone can help me?


Comment by: Jan Karel Pieterse (31-10-2011 01:07:57) deeplink to this comment

Hi RealParadox,

What exactly is the problem?


Comment by: Vivek Gokhale (3-11-2011 18:16:56) deeplink to this comment

Hi!!!

This article was great.

Is it possible to wrap text data in the slicer button???

Would really help.

Thanks


Comment by: Jan Karel Pieterse (4-11-2011 05:07:14) deeplink to this comment

Hi Vivek,

I'm afraid not!


Comment by: Melroy Beeks (6-11-2011 15:21:11) deeplink to this comment

In my Micro

soft does not have a SLICER button


Comment by: Jan Karel Pieterse (8-11-2011 00:53:15) deeplink to this comment

Hi Melroy,

You need to have Excel 2010 to be able to use Slicers in Pivot tables.


Comment by: s hartman (18-1-2012 13:56:22) deeplink to this comment

How can I use one slicer when I have multiple pivotcaches (and multiple pivot tables)?


Comment by: Jan Karel Pieterse (18-1-2012 23:38:18) deeplink to this comment

Hi Seana,

I'm afraid you cannot tie one (set of) slicer(s) to more than one Pivot cache. However, you can use a bit of VBA code to mimick that behaviour. Find some examples in the comments of the next page:

https://jkp-ads.com/Articles/slicers03.asp?AllComments=True


Comment by: Dipti (20-1-2012 09:21:40) deeplink to this comment

Can i put slicer on the same page as the Pivot table?. I am trying to make it look like how we select from the drop down and get the related information in pivot table..


Comment by: Jan Karel Pieterse (22-1-2012 22:24:18) deeplink to this comment

Hi Dipti,

Of course you can!


Comment by: Jason Bisulco (25-1-2012 06:13:50) deeplink to this comment

Can you group the data in a slicer? (e.g. in number incremeents say 1 - 20 or group monday - wednesday if a date.


Comment by: Jan Karel Pieterse (25-1-2012 07:38:59) deeplink to this comment

Hi Jason,

No, but you can group the appropriate field of the pivot table and add a slicer for the grouped field.


Comment by: Andre (27-1-2012 10:45:01) deeplink to this comment

Hi Jan Karel,

Is it possible to add value filters, i.e. values greater than 1,000,000 to the slicers?

If so, what about multiple value filters?

Cheers,
Andre


Comment by: Jan Karel Pieterse (27-1-2012 11:11:42) deeplink to this comment

Hi Andre,

I don't think ou can do that, no.


Comment by: Kajsa-Stina Ohlström (3-2-2012 07:40:48) deeplink to this comment

When i should connect my slicer to more then one of my pivot tables, there comes a message that it "the slicer can not currenty be created or connected to the pivot table". What's wrong ?


Comment by: David (4-2-2012 09:08:40) deeplink to this comment

Thank you so much for this resource! I have been searching everywhere for a way to link my slicers that do not share a cache with no success until now!

I modified the code provided to work with three slicers that have different caches. Unfortunately I have to run the "For Each" section twice, otherwise about 20% of the time it selects all of the slicer fields, or nearly all of them. Any idea why?

Dim mbNoEvents As Boolean
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oSl As SlicerItem
    If mbNoEvents Then Exit Sub
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    If Sh.Name = "Sheet1" Then
        mbNoEvents = True
        With SlicerCaches("Slicer1")
            For Each oSl In .SlicerItems
                    SlicerCaches("Slicer2").SlicerItems(oSl.Caption).Selected _
                        = oSl.Selected
                    SlicerCaches("Slicer3").SlicerItems(oSl.Caption).Selected _
                        = oSl.Selected
            Next
            For Each oSl In .SlicerItems
                    SlicerCaches("Slicer2").SlicerItems(oSl.Caption).Selected _
                        = oSl.Selected
                    SlicerCaches("Slicer3").SlicerItems(oSl.Caption).Selected _
                        = oSl.Selected
            Next
        End With
    End If
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    mbNoEvents = False
End Sub


Comment by: Kajsa-Stina (5-2-2012 23:46:01) deeplink to this comment

Hi
When I try to add more than one pivot table connections to my slicer, I got a message that "An error occured and the slicer can not currently be created or connected to pivot tables". What's wrong ?


Comment by: Jan Karel Pieterse (6-2-2012 04:57:24) deeplink to this comment

Hi Kajsa-Stina,

You can only add pivot tables form the same pivot table cache to one slicer, perhaps the pivot tables you are trying to combine are independently tied to their own source?


Comment by: Kajsa-Stina (13-2-2012 07:41:02) deeplink to this comment

Thank you for the answer.
All pivot tables i try to connect to my slicer are using an area that I've named "rawdata" as datasource. This area is just in another sheet in the same workbook.
When checking name manager, I can see that "rawdata" is right defined, however, my slicers are also listed in the name manager, but they have no reference...
/Kajsa-Stina


Comment by: Jan Karel Pieterse (13-2-2012 08:12:58) deeplink to this comment

Slicers do not have a reference in Name Manager, that is "by design". How many Pivot caches does your workbook have?
Alt+F11 to the VBA editor
control+g to the immediate window
type:

?ActiveWorkbook.PivotCaches.Count

press enter with your cursor on that line.


Comment by: Kajsa-Stina (14-2-2012 00:48:22) deeplink to this comment

I've just 1 Pivot Cache (getting that after doing as you told)


Comment by: Jan Karel Pieterse (14-2-2012 03:00:29) deeplink to this comment

Hi Kajsa-Stina,

In that case I truly have no idea why it does not work.

Perhaps you should try to recreate the pivot tables in a blank workbook just to check if it does work?


Comment by: Kajsa-Stina (14-2-2012 04:45:44) deeplink to this comment

I've solved my problem by "remaking" all pivot-tables by copy/paste for all tables that should be connected to the same slicer. Now it works.


Comment by: Jan Karel Pieterse (14-2-2012 07:03:37) deeplink to this comment

Hi Kajsa-Stina,

Excellent, glad you were able to solve the problem!


Comment by: Aaron (20-2-2012 14:01:12) deeplink to this comment

Once you've created a custom slicer template, is it possible to save it and access it on another workbook? I've created my own slicer template (color, font, shading, etc) that I want to use in different workbooks, but the "created" templates are only available within that one workbook.
Cheers,
Aaron


Comment by: Jan Karel Pieterse (20-2-2012 22:54:55) deeplink to this comment

Hi Aaron,

A quick way is to open the workbook with the custom slicer style applied to a slicer (not sure of that is necesary) and copy the slicer and paste it into the other workbook. The slicer style is copied with it.


Comment by: Aaron (22-2-2012 15:36:32) deeplink to this comment

Cheers! That is quite simple and works perfectly!


Comment by: Midimal (28-2-2012 04:02:22) deeplink to this comment

Hi! Does anyone know how to determine using VBA if slicer filter is on or not? (similar what is possible with autofilter)

thanks


Comment by: Jan Karel Pieterse (5-3-2012 05:12:11) deeplink to this comment

Hi Midimal,

YOu could use this function to detect whether or not a slicers is in filter mode:

Function IsSlicerFiltered(oSl As Slicer)
    Dim oSi As SlicerItem
    For Each oSi In oSl.SlicerItems
        If oSi.Selected = False Then
            IsSlicerFiltered = True
            Exit Function
        End If
    Next
End Function


Comment by: Irvin Jaffe (7-3-2012 12:32:47) deeplink to this comment

RE: Excel 2010. I have a slicer with 6 choices. I want to show only 4 of the 6 (2 of the 6. And, of the 4 that I want visible, I want to change the text. How do I do this?


Comment by: Jan Karel Pieterse (8-3-2012 02:39:30) deeplink to this comment

Hi Irvin,

I think you can only achieve that by removing data from the source so that those items are not on the slicer. Also, you cannot change what is shown on the slicer, other than by changing the source data.


Comment by: bill (29-3-2012 15:19:18) deeplink to this comment

Can someone tell me why I receive an 'application defined or object defined error' when I try to set a slicer selected mode to 'True' or 'False'? (example in below code: oSi.selected = True)

Dim oSi As SlicerItem
Dim oSl As SlicerCacheLevel
Dim selected As Integer
selected = 0
Dim slicerDateVal As Date
Dim slicerDateText As String
    
    
Dim i As SlicerCaches
Dim k As Slicer
    
    Application.EnableEvents = False
    'ActiveWorkbook.SlicerCaches("Slicer_Attendance_Date").ClearManualFilter
     For Each oSl In ActiveWorkbook.SlicerCaches("Slicer_Attendance_Date").SlicerCacheLevels
         For Each oSi In oSl.SlicerItems
             slicerDateVal = oSi.Caption
             slicerDateText = Format(slicerDateVal, "yyyy-mm-dd")
             If slicerDateVal >= startDate And slicerDateVal <= endDate Then
                'oSi(slicerDateVal).selected = True
             If oSi.selected = False Then
                oSi.selected = True
             End If
            


Comment by: Jan Karel Pieterse (30-3-2012 01:54:14) deeplink to this comment

Hi Bill,

Not sure why, but perhaps the item in question is greyed out on the slicer because of other filters being in place?


Comment by: Mark F. (3-4-2012 09:26:35) deeplink to this comment

I cannot find the spot to change the format of numbers in the slicer. Currently I am getting very generic looking numbers all left justified. Any solutions to this?


Comment by: Jan Karel Pieterse (3-4-2012 21:54:29) deeplink to this comment

Hi Mark,

The number format of a slicer cannot be adjusted I'm afraid.


Comment by: Kathryn (3-5-2012 10:08:53) deeplink to this comment

How do I change the date format in a slicer button? My table data has the date as 6/1/2011, but I've formatted it to display Jun-11. However, the slicer button still displays 6/1/2011.


Comment by: Jan Karel Pieterse (3-5-2012 11:18:35) deeplink to this comment

Hi Kathryn,

I just tried this and it looks like the slicer adheres to the date format as set in the source data table.


Comment by: Rob (16-5-2012 06:20:02) deeplink to this comment

I have slicers on one tab and pivot tables on another.

When I copy these tabs to a new workbook, my slicers do not copy (or macro buttons for that matter). They used to copy fine when I went through this process, but I must have changed something along the way.

Is there a way to reset this function ?

Thanks,
Rob


Comment by: Jan Karel Pieterse (16-5-2012 07:53:58) deeplink to this comment

Hi Rob,

To be honest, I have no idea! Sounds like your file may be experiencing problems?

Are you copying all sheets that belong to each other in one go?


Comment by: Rob (17-5-2012 04:03:16) deeplink to this comment

Hi Jan,

I have tried the copy process using all the tabs and just single tabs. I have also tried it using the tab drag process from one open workbook to another and still lose my slicers and buttons.

May have create a different work around....


Comment by: Dan (17-5-2012 04:46:15) deeplink to this comment

Hi Jan,

I am trying to make one slicer change when another similar slicer changes. This is like other people above because I have information from more than one data source. I have ammended your code above for my workbook, the sheet is called "WWNI dashboard" and my slicers are called "ww_network_manager 3" and "ww_network_manager 4"

So I have this code in the "ThisWorkbook"



Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oSl As SlicerItem
    If mbNoEvents Then Exit Sub
    'IN case a slicer item in slicer 1 is not present in slicer 2
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    If Sh.Name = "WWNI dashboard" Then
        mbNoEvents = True
        With SlicerCaches("ww_network_manager 3")
            For Each oSl In .SlicerItems
                If SlicerCaches("ww_network_manager 4").SlicerItems(oSl.Caption).Selected _
                 <> oSl.Selected Then
                    SlicerCaches("ww_network_manager 4").SlicerItems(oSl.Caption).Selected _
                        = oSl.Selected
                End If
            Next
        End With
    End If
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    mbNoEvents = False
End Sub

Nothing happens when I change the "ww_network_manager 3" slicer. Can you help identify what I am doing wrong please?

Thanks,
Dan


Comment by: Jan Karel Pieterse (18-5-2012 07:21:55) deeplink to this comment

Hi Dan,

What happens if you put a breakpoint in the code (first possible line) and then change the slicer that is supposed to trigger the event?


Comment by: Dan (18-5-2012 12:01:08) deeplink to this comment

If I put a breakpoint on "If mbNoEvents Then Exit Sub" line and click the slicer it goes to my vba, I can then step through line by line and it runs fine. I have made a few modifications below to make this happen as my sheetname was the name of the sheet where the slicers are, however it does not change the slicer selections. My pivots are made with powerpivot, I'm not sure if that makes a difference. I can send a copy of the spreadsheet if you like.


Dim mbNoEvents As Boolean

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oSl As SlicerItem
    If mbNoEvents Then Exit Sub
    'IN case a slicer item in slicer 1 is not present in slicer 2
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    If Left(Sh.Name, 8) = "Data for" Then
        mbNoEvents = True
        With SlicerCaches("Slicer_ww_network_manager3")
            For Each oSl In .SlicerItems
                If SlicerCaches("Slicer_ww_network_manager4").SlicerItems(oSl.Caption).Selected _
                 <> oSl.Selected Then
                    SlicerCaches("Slicer_ww_network_manager4").SlicerItems(oSl.Caption).Selected _
                        = oSl.Selected
                End If
            Next
        End With
    End If
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    mbNoEvents = False
End Sub


Comment by: Jan Karel Pieterse (20-5-2012 23:18:34) deeplink to this comment

Hi Dan,

What happens if you remove the On Error Resume Next statement? It will mask any problems in the code.


Comment by: T Pearce (22-5-2012 14:30:49) deeplink to this comment

I am using slicers in a financial application I created and it works great filtering the data, but when you drill down to see the details, it does not filter the data at all. Can you give me some insight on how to prevent this from happening. I don't understand why it does not filter the detail data.


Comment by: Jan Karel Pieterse (23-5-2012 23:14:45) deeplink to this comment

Hi T.,

This is one of those situations where a sample file is needed to see what is going on. Please use the email address below to send me one (if possible).


Comment by: katia (25-5-2012 14:24:56) deeplink to this comment

Hi Eileen,

I am preparing for my next class. In my Pivot Table -> Options -> Sort, the "Insert Slicer" is not shown at all.

Can you help me?

Thank you so very much in advance.


Comment by: Jan Karel Pieterse (26-5-2012 02:56:53) deeplink to this comment

Hi Katia,

Eileen is not my name :-), it is the name of a forum where you can ask Excel questions.

To your question: Do you have Excel 2010? Slicers are not available in older Excel versions.


Comment by: Lizzie (7-6-2012 23:11:14) deeplink to this comment

I have created my slicer and formatted it to match my table but I need to display only months and not the full date so that I can sort my table by month. How do I modify my date so that it only shows the month and not the full date? Thanks


Comment by: Jan Karel Pieterse (8-6-2012 11:58:19) deeplink to this comment

Hi Lizzie,

I expect you can do this by first grouping your date field in the pivot table by months and then inserting a new slicer for the month.


Comment by: Hank Buonforte (23-6-2012 01:09:17) deeplink to this comment

Does anyone know how I can get a slicer search bar? I want to be able to type something into the search bar and press enter and have it select what I typed in the slicer.


Comment by: Jan Karel Pieterse (25-6-2012 07:08:25) deeplink to this comment

Hi Hank,

Unfortunately, no there isn't one. Would have been a great addition!


Comment by: Anwaar (2-7-2012 06:55:00) deeplink to this comment

Hi

When I tried to add more than one pivot table connections to my slicer, I get a message stating "An error occured and the slicer can not currently be created or connected to pivot tables". Why I am I unable to connect? I've just 1 Pivot Cache (checked as per your instructions above.

I am facing the same problem as Kajsa-Stina (2/5/2012 11:46:01 PM). I was curious to know if you have found a work around to it instead of re-creating all the tables.

Please advise.


Comment by: Jan Karel Pieterse (2-7-2012 11:11:26) deeplink to this comment

Hi Anwaar,

Perhaps Excel thinks that the pivot table was created in an older version of Excel (I have seen that happen to files which were created in Excel 2007). Try recreating the pivot table. Also: make sure the file is NOT saved in Excel 97-2003 file format.


Comment by: Steve (5-7-2012 14:25:19) deeplink to this comment

My fiscal year runs from July to June. After creating my pivot table and inserting a slicer for the months, the slicer automatically sorts it from January to December. I cannot find an option to customize the sort order of the slicer.

Thanks for any help you can give me.


Comment by: Jan Karel Pieterse (5-7-2012 19:28:37) deeplink to this comment

Hi Steve,

Perhaps you can add a custom sort order in the custom lists item in Excel Options?


Comment by: Steve (12-7-2012 16:59:57) deeplink to this comment

Having created a custom slicer style in a workbook, how do you export that style to be used in another workbook?.

Thanks



Comment by: 100tsky (16-7-2012 02:15:52) deeplink to this comment

Hi!

Can I get different format of measure based on value of slicer items, something like that:

If SlicerItem = "abs" Then


With ActiveSheet.PivotTables("PP01").PivotFields( _
        "[Measures].[sum]")
        .Calculation = xlNormal
        .NumberFormat = "# ### ##0_ð_."
    End With
End If

If SlicerItem = "ratio" Then

With ActiveSheet.PivotTables("PP01").PivotFields( _
        "[Measures].[sum]")
        .Calculation = xlPercentOfColumn
        .NumberFormat = "0.0%"
    End With
End If


thank you!!!


Comment by: Jan Karel Pieterse (7-8-2012 11:35:45) deeplink to this comment

Hi 100tsky,

I doubt if you can do that, but you could tie the code you show to two buttons to mimick the behaviour?


Comment by: Jan Karel Pieterse (7-8-2012 11:37:48) deeplink to this comment

Hi STeve,

You can copy a slicer with that style to the new workbook and after that delete the slicer (I think).


Comment by: David (15-8-2012 13:22:42) deeplink to this comment

I am trying to use slice, but when I go to set it up the slice icon is dimmed in the tool bar. Am I missing something? How do i get the slice icon to enable?
David


Comment by: Jan Karel Pieterse (15-8-2012 18:29:15) deeplink to this comment

Hi David,

The pivot table you are trying to connect to a slicer must be in a file create by Excel 2010 and saved in the 2010 file format (xlsb, xlsx or xlsm). Pivot tables created in earlier versions or in files of older Excel versions cannot be combined with a slicer.


Comment by: Meggan (17-8-2012 00:22:42) deeplink to this comment

I have Excel 2010 with Win 7 -- I can neither modify nor delete custom styles (either Pivot Table or Slicer) and it's driving me CRAZY! The options are available for me to click, but nothing happens after I do. Seen it? Got a fix?

Thanks!


Comment by: Jan Karel Pieterse (17-8-2012 11:53:06) deeplink to this comment

Hi Meggan,

Odd indeed. Perhaps there are slicers/tables that have the custom style applied to them, making Excel refuse to remove the style?


Comment by: Chris (21-8-2012 00:08:12) deeplink to this comment

Okay, when I first saw slicers, I thought, yeah, they're nice in that they look better than pivot table report filters, but that's really all they are - nicer looking pivot table report filters.

Then I read this article and discovered that they will apply your hierarchical groupings to your filtering. Okay. That's cool. I think more of slicers now than I did two minutes ago (I'm a slow typer ;-))


Comment by: Carlos (8-9-2012 00:52:17) deeplink to this comment

I have a pivot table in which I use two fields as report filters. The first field is a set of item groups, and the second field is a set of items contained in an item group. The various item groups have differrent sets of items. I would like to set up two slicers (item group, and item) such that when I pick a value in the item group slicer, the item slicer only shows the values appropriate for the selected item group. Can this be done, and if so how. I appreciate your help. Thanks.


Comment by: Jan Karel Pieterse (10-9-2012 10:02:04) deeplink to this comment

Hi Carlos,

Slicers offer that option by default, but how they behave when items are not applicable is something you can control in the slicer options dialog, which is accessible through a right-click on the slicer and select "Slicer settings".

However, you cannot set it to not show non-applicable items, you can only make sure those are shown at the bottom of the list and in a different color.


Comment by: Jacob Jørgensen (12-9-2012 11:39:17) deeplink to this comment

Hi,

I have designed a pivottable, and I want to use the slicer tools. My idea is to use a togglebutton, so if it is activated it will show the slicer, and deactivated it will not appear. Is that possible?

Hope you understand.

Kind regards

Jacob


Comment by: Jan Karel Pieterse (13-9-2012 09:48:16) deeplink to this comment

Hi Jacob,

Sure that is possible, but it requires a bit of VBA which you tie to the toggle button (though I would probably a checkbox instead).
I added a checkbox from the forms toolbar to a worksheet. The checkbox's name is "Check Box 1". I assigned this macro to the checkbox:

Sub CheckBox1_Click()
    Dim oSl As Slicer
    Dim oSc As SlicerCache
    For Each oSc In ThisWorkbook.SlicerCaches
        For Each oSl In oSc.Slicers
            If oSl.Parent.Name = ActiveSheet.Name Then
                If ActiveSheet.CheckBoxes("Check box 1").Value = -4146 Then
                    oSl.Height = 0
                Else
                    oSl.Height = 100
                End If
            End If
        Next
    Next
End Sub


Note that you cannot hide a slicer, only set its heigh or width to zero. This means if you want to show the slicer again, you must remember what its original height was. I did not include any logic in this macro to handle that though.


Comment by: Mark (25-9-2012 11:10:26) deeplink to this comment

I am trying to copy a slicer from one workbook to another. The slicer i pasted is no longer linked to the pivotable on the original file i copied from. How can i do it?


Comment by: Jan Karel Pieterse (25-9-2012 13:24:06) deeplink to this comment

Hi Mark,

Looks like a slicer cannot be tied to a pivotcache in a different workbook.


Comment by: Michelle (26-9-2012 15:53:51) deeplink to this comment

Hi,

One of my slicers contains a significantly long list of names. I realize that if I need to do a search I can do so in the pivot table itself. However, is there a way I can give the user the ability to do this in the slicer?

By the way, using Excel 2010.

Thanks in advance!


Comment by: Jan Karel Pieterse (26-9-2012 19:15:07) deeplink to this comment

Hi Michelle,

I know, that is a PITA. The only way is the one you wrote about.


Comment by: Paul Kelly (2-10-2012 11:47:39) deeplink to this comment

Would like to show what slicers (filters have been applied) e.g. If Customer filter and Joe Bloggs and AN Other have been selected - show at the top the slicer that has been applied.


Comment by: Jan Karel Pieterse (2-10-2012 13:13:21) deeplink to this comment

Hi Paul,

Well, of course the pivot table itself does show whether or not filters have been applied and if it is only one item, it shows the item.

I seem to recall one of the CUBE worksheet functions can be tied to a slicer, but I have not tried this, nor do I know whether this can be done to any slicer.


Comment by: Rebecca Haack (17-10-2012 20:20:01) deeplink to this comment

Can you get a total for the slicer's selection? For example, If I have the slicer set for January, can I get a total for all the amounts the slicer has selected for January?


Comment by: Jan Karel Pieterse (18-10-2012 11:03:02) deeplink to this comment

Hi Rebecca,

The pivot table shows the total for the slicer's selection, provided you have included totals in your pivot table of course.


Comment by: Ricardo Madaleno (18-10-2012 15:04:53) deeplink to this comment

Hi,

I have an issue. Whenever I select one of the filters in my slicer, it changes the height of my header rows...

Is there a way to fix this?

Thanks


Comment by: Jan Karel Pieterse (19-10-2012 12:00:22) deeplink to this comment

Hi Ricardo,

Perhaps you can fix this by changing the pivot table's settings regarding keeping formatting and adjusting column widths?


Comment by: Ricardo Madaleno (19-10-2012 13:48:20) deeplink to this comment

Hi Jan,

no, that's not it... i have my pivot with the option to autofit unchecked.

this is very strange... can't seem to fix it.


Comment by: Jan Karel Pieterse (19-10-2012 19:08:39) deeplink to this comment

Hi Ricardo,

If the slicer is filtering the page filter, perhaps the column where the page filter is located is too narrow for certain values If that cell has word wrap on, it will force Excel to increase row height to display the full value.


Comment by: Anne (30-10-2012 05:45:19) deeplink to this comment

Hi!

I have been using slicers as a great way to indicate what areas of our business that needs attention. I want to do this once a month. But when I try to update the pivot table/ refresh, nothing changes in the slicers. Help would be much appropriated :) Anne


Comment by: Jan Karel PIeterse (30-10-2012 08:57:14) deeplink to this comment

Hi Anne,

There should be an option in the slicer settings that allows you to indicate whether or not to have the slicer include items which are no longer in the pivotcache. Is that what you are after?


Comment by: Anne (30-10-2012 11:04:05) deeplink to this comment

Thank you for your reply.

This is the message I get when I try to update my with the slicers; "The data source of a PivotTable connected to the slicers that are also connected to the Pivot Tabel cannot be changed. To change the data source first disconnet the slizers from this and other pivottables".

So my question to you is, is there any way to get around this? I would really like to keep my slizers and not have to do them wach time i update my pivottable.

Thanks again!


Comment by: Jan Karel PIeterse (30-10-2012 12:06:14) deeplink to this comment

Hi Anne,

Odd, I just created a PT bound to an Access database, with one slicer connected to it. If I change the PT source (by editing the connection properties), it just lets me do that without complaining.


Comment by: Oz (13-11-2012 11:05:40) deeplink to this comment

Hi Jan,

Like Ricardo I'm having problems with my slicers resizing my pivot table, though mine are changing the column width:

-My table is set to not resize on update.
-The values in my table are never more than 2 digits.
-The row labels are abbreviated month names.

-It doesn't matter where on the sheet the slicer is.
-It doesn't help if I stretch the columns out to allow some extra room.
-Whenever I change the months the row label column resizes.

I think it may be seeing the full name of the month even though it shows the abbreviated name, but if I stretch it to allow room for this it'll shrink for shorter month names.

Thanks for your help


Comment by: Jan Karel Pieterse (13-11-2012 11:35:51) deeplink to this comment

Hi Oz,

Can you perhaps email that workbook to me (pls refer to this page when doing so so I won't discard that email as spam)?


Comment by: Ram (13-11-2012 16:15:34) deeplink to this comment

Hi Jan,

Awesome job... I read through every post. Its really a task to keep replying.. But We all truly appreciate.

I read through all the post but may have missed this.
I have a slicer and a pivot chart which is a combo with line and bar. Whenever I select an option from slicers it changes the line chart back to bar. Can you help to resolve this.
Thanks again.


Comment by: Oz (13-11-2012 16:26:01) deeplink to this comment

Hi Jan,

Thanks for getting back to me so quickly.

I'm afraid I can't send it to you as it contains sensitive company data. I tried to remove all the personal data and give you a version with just the sheets in question, and what do you know; the problem went away!

Still there on the original though :(


Comment by: Oz (13-11-2012 16:29:43) deeplink to this comment

Well that's embarrasing;

I had a feeling the gentleman who created this workbook might be behind the fault so I looked through the page and found, right at the bottom, another pivot table, with autofit on!

Well, at least its solved, and I've got another horror story to share with the wife :)

Thanks again Jan


Comment by: Rebecca (13-11-2012 16:34:12) deeplink to this comment

Hi Jan,

Did you find a solution to the problem posted by T Pierce on 5/22/12? I am having the same problem. I want to have slicers in a worksheet that are linked to multiple pivot tables and charts, and be able to drill into the details for just the results filtered by the slicers. Thanks!


Comment by: Jan Karel Pieterse (13-11-2012 19:07:52) deeplink to this comment

@Oz: Thanks for letting me know, another mystery solved.

@Rebecca: No, but perhaps you can ask your question over at Eileen's lounge?

@Ram: I think the only way is to add a bit of VBA code that resets the chart after filtering.


Comment by: Trish McDermott (7-12-2012 00:11:04) deeplink to this comment

Hi there!
thanks for a really simple explanation on slicer cache...well done.

Now I don't want a message box to appear, but the slicer value to appear in a cell... how can I do this? I'm tearing my hair out!

It is probably really simple, I must be missing something.

I have one pivot table and 1 slicer in my workbook.

Cheers
Trish


Comment by: Jan Karel Pieterse (7-12-2012 13:04:56) deeplink to this comment

Hi Trish,

Like so perhaps?

Sub GetSlicerValues()
    Dim lCt As Long
    Dim oSi As SlicerItem
    Worksheets.Add
    With ActiveSheet.Range("A1")
        .Value = "Slicer value"
        .Offset(, 1).Value = "Selected"
        .Offset(, 2).Value = "Available"
        For Each oSi In ActiveWorkbook.SlicerCaches("Slicer_City").SlicerItems
            lCt = lCt + 1
            .Offset(lCt).Value = oSi.Value
            .Offset(lCt, 1).Value = oSi.Selected
            .Offset(lCt, 2).Value = oSi.HasData
        Next
    End With
End Sub


Comment by: Mark Fay (7-12-2012 17:55:29) deeplink to this comment

one big problem I can't get past with slicers is that that deleting or modifying a custom slicer does not work. The only way I have of modifying a customer slicer is to duplicate it, the modify box then pops up. I now have 11 custom slicers in various stages and cannot delete any. Is there some trick to get modify and delete to work.

The prompt dialogue boxes come up but don't actually do anything.


Comment by: Bill (8-12-2012 23:42:53) deeplink to this comment

Thank you for these explinations. I like the last code snippet you gave to Trish. When combined with a "Worksheet_PivotTableUpdate" Subroutine the code helped me understand the slicer actions better.


Comment by: Bill (9-12-2012 00:25:41) deeplink to this comment

So my question would be... if I have the code you suggested for Trish wrapped in a "Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)" subroutine, how could I have it cycle through and print out information for all of the slicers associated with the PivotTable that was associated with the slicer that activated the macro. Also, I can't seem to get out the "Parent" value for a slicer item. I keep getting "Run-time error '1004' Application-defined or object-defined error". Thanks in advance.


Comment by: Jan Karel Pieterse (10-12-2012 08:29:16) deeplink to this comment

Hi Bill,

Actually, it is the pivot table that activates the event routine, not the slicer. Anyway, perhaps this gets you going:

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oSl As SlicerCache
    Dim oPt As PivotTable
    For Each oSl In SlicerCaches
        For Each oPt In oSl.PivotTables
            If oPt.Name = Target.Name Then
                MsgBox "The pivot " & oPt.Name & " has " & oSl.Name & " associated with it"
            End If
        Next
    Next
End Sub


Comment by: Jan Karel Pieterse (10-12-2012 08:33:16) deeplink to this comment

I expect you are referring to a custom slicer style?
Some important things to know:

1. You can only modify a custom slicer style. Built-in styles cannot be modified.

2. (and I think this is the one you need!) After duplicating a built-in style and modifying it, you have to apply the new custom slicer style to the slicer in question. Only then do your modifications appear.


Comment by: Mark Fay (10-12-2012 08:56:05) deeplink to this comment

thanks Jan, yes it is a custom style. I can modify after duplicating but then cannot modify it again unless I duplicate again. Also cannot delete it. I want to modify a style check it then make more modifications until I have what I want.

Any ideas?


Comment by: Jan Karel Pieterse (10-12-2012 09:00:13) deeplink to this comment

Hi Mark,

I can modify them as much as I like after creating a custom one. I can also delete them, even though they are in use on a slicer.
Perhaps your worksheet/workbook is protected?


Comment by: Obwan (11-1-2013 05:54:19) deeplink to this comment

Hi
I saved my Excel 2010 workbook with 10 pivot tables connected to 1 slicer. Works perfectly. Now I have added additional pivot tables but cannot get them to list in the slicer connections. Is there a limit to the number of connections or is there a trick I am missing. Thank you in advance.


Comment by: Jan Karel Pieterse (11-1-2013 16:30:48) deeplink to this comment

Hi Obwan,

The trick is that the pivot tables must all share the same pivot cache.


Comment by: Amir (17-1-2013 18:01:31) deeplink to this comment

Hi
I am using pivot tables and slicers to create dashboard reports. The info you have provided on this web page is fantastic and very well put together.

I have a large pivot table with fields such as cost centres, cost, date and product code. I have created slicers for cost centres and product code.

The individual cost centres are grouped into North Division and South Division.

My question is can I create slicers for individual cost centres, all North Division cost centres, all South Division cost centres and finally whole Group cost centres? I hope this makes sense! Would very much appreciate your thoughts or advice.


Comment by: Jan Karel Pieterse (18-1-2013 16:33:52) deeplink to this comment

Hi Amir,

No, all you can do is put a slicer up there that filters on Division. The cost center slicer will then (if properly setup) show the relevant cost centers at the top and the other ones greyed out near the bottom.


Comment by: Bob (24-1-2013 09:40:12) deeplink to this comment

Thanks for the really detailed descriptions on how to work with slicers.

I would like to add one question:
Is there a chance to define which selectable buttons in a slicer are shown? Can you specify a slicer filter to be hidden?
e.g.: My slicer shows 4 options "Products active", "Products inactive", "Products to be developed"
I would like to hide only provide "Products active", "Products inactive" to be selectable.

Best regards
Bob


Comment by: Jan Karel Pieterse (24-1-2013 10:19:26) deeplink to this comment

Hi Bob,

The only way to do that is to have a separate field in the pivot table's source data which would filter out the item you do not want in the slicer. Then you must set up the slicer to hide non-applicable items and filter the pivot table on the field that affects which slicer items apply.


Comment by: Bob (24-1-2013 18:51:55) deeplink to this comment

Hi Jan,

thanks for your quick response.
I tried and it may work, but I cannot tell my slicer to not show non-applicable items.
The only way I could do it is to define the color scheme for items with no data so that the button will become invisible.


Comment by: Narayan (30-1-2013 07:32:51) deeplink to this comment

Can I connect two slicers (having the same dataset)?
My requirement is to change the data in slicer2 depending on the input from Slicer1. Accordingly the data in the pivot tables change.


Comment by: Jan Karel Pieterse (30-1-2013 12:51:52) deeplink to this comment

Hi Narayan,

I'm not sure what you mean by "connecting" them, the slicers are connected and do reflect filters of other slicers (of you have set the properties accordingly).


Comment by: Tiana (7-2-2013 05:34:00) deeplink to this comment

I need to go through the slicer items one by one and activate each option so I can save the result as a pdf file.

The following code activates each option but when it moves onto the next one it keeps the previous one selected:

Sub GetSlicerValues()
    Dim oSi As SlicerItem
    For Each oSi In ActiveWorkbook.SlicerCaches("Slicer_cd_merchant_id").SlicerItems
    oSi.Selected = True
    Next
End Sub


Is there a way to select one a time?

Thanks,

T


Comment by: Jan Karel Pieterse (7-2-2013 20:33:57) deeplink to this comment

Hi Tiane,

You could do it like this:

Sub GetSlicerValues()
    Dim oSi As SlicerItem
    For Each oSi In ActiveWorkbook.SlicerCaches("Slicer_cd_merchant_id").SlicerItems
     oSi.Selected = True
     'Now print to PDF
     oSi.Selected = False
    Next
End Sub


Comment by: Stephen (9-2-2013 00:06:03) deeplink to this comment

Is there any way to save your custom slicer styles so they are available across workbooks?


Comment by: Jan Karel Pieterse (10-2-2013 20:34:21) deeplink to this comment

Hi Stephen,

If you copy a slicer with that slicer style applied to a fresh workbook and then save-as that workbook as book.xltx (or m) to your xlstart folder the slicerstyle should become available to each new workbook you start with control+n.


Comment by: Beth (13-2-2013 01:53:52) deeplink to this comment

I am sending out a pivot table to several folks and I'm using my first slicer. Lovely tool. However, I typically protect the sheet to protect those folks from themselves. :)
You know, accidentally changing up the columns, etc. When I do that, the slicer stops allowing the user to select a different item, even tho the users of the pivot table can still change the sort or perform additional filtering within the pivot table itself. Is the slicer not considered part of the pivot table when I select my protection options?


Comment by: Jan Karel Pieterse (13-2-2013 08:54:14) deeplink to this comment

Hi Beth,

This can be done, but it is tricky:

Go to the “size and properties” dialog and uncheck the “locked” property for the slicer, THEN protect the sheet with “use PivotTables” (if you’re using pivot slicer) or “use autofilter” (if you’re using table slicer in Excel 2013).


Comment by: Arjo (15-2-2013 10:19:30) deeplink to this comment

Hi,

I use slicers a lot. I used to be able to copy them around my excel file.
Today I was able to create a slicer at the pivottable sheet, but I could not copy paste it to my pivotgraph sheet. It was pasted as an image.

I also do not have the opportunity to add a slicer when I have the graph selected. Something which normally is possible.

Is there something I can do to fix this?


I need the slicers a lot for creating a dashboard but I have to be able to move the slicers around my excel file.

thanks!


Comment by: Jan Karel Pieterse (15-2-2013 15:53:10) deeplink to this comment

Hi Arjo,

I haven't heard of that problem before. Is the chart a chart embedded on a sheet or on a chartsheet? Perhaps chartsheets cannot hold slicers?
Perhaps the file/sheet is protected?


Comment by: Arjo (18-2-2013 11:03:13) deeplink to this comment

I am not 100% sure, but I believe that chart sheets cannot hold slicers.

I placed the graph on a regular sheet and everything worked fine.

Although it would be nice to use slicers with multiple data sheets, I know this is not going to happen any soon. So for this moment I will have to create multiple slicers.


Comment by: Jan Karel Pieterse (18-2-2013 11:54:28) deeplink to this comment

Hi Arjo,

What do you mean by using slicers with multiple data sheets?


Comment by: Arjo (18-2-2013 13:28:22) deeplink to this comment

I'd like to connect 1 slicer to pivottables from different data sources. something which is not possible...

for example: I have 2 data sources. data sheet 1 and data sheet 2. In both the field 'account' is available and in both the values are the same.
I want to have 1 slicer to with which I can filter the account value in all the connected pivottables.


Comment by: Jan Karel Pieterse (18-2-2013 17:07:33) deeplink to this comment

Hi Arjo,

Check out this page, in the comments there should be some code to tie two slicers together.

https://jkp-ads.com/Articles/slicers03.asp


Comment by: Debbie Leonard (21-2-2013 04:01:32) deeplink to this comment

I have a button that clears all my slicers filters...it works great, but I'd like to add code to have all slicers scroll back to the top of each slicer list. I've been looking for days but no luck finding an answer. I'm using Slicers with Excel 2010, not PowerPivot.
Thanks.


Comment by: Jan Karel Pieterse (21-2-2013 09:55:29) deeplink to this comment

Hi Debbie,

What you could try is first setting the filter to the first item in the slicer, before removing the filter.


Comment by: Debbie Leonard (26-2-2013 01:17:18) deeplink to this comment

How do I set the filter to the first item in each of my slicers?
Thanks,
Debbie


Comment by: Jan Karel Pieterse (26-2-2013 08:52:37) deeplink to this comment

Hi Debbie,

It took me some digging, but I found a (rather kludgy) way to scroll the slicer to the top. Like so: (note that this also clears the filters)

Sub SetSlicerToFirstItem()
    Dim oActive As Worksheet
    Dim oSi As SlicerItem
    Dim oSc As Slicer
    Dim oPt As PivotTable
    Dim oSh As Worksheet
    Set oActive = ActiveSheet
    For Each oSh In Worksheets
        oSh.Activate
        For Each oPt In oSh.PivotTables
            For Each oSc In oPt.Slicers
                For Each oSi In oSc.SlicerCache.SlicerItems
                    oSi.Selected = True
                Next
                'Activate a cell
                ActiveCell.Activate
                'Prepare to send some keystrokes
                SendKeys "{TAB}{HOME}"
                'Select the slicer object on the worksheet
                oSh.Shapes(oSc.Caption).Select
                'Now send the keys to that object
                DoEvents
            Next
        Next
    Next
    oActive.Activate
End Sub


Comment by: Yan (12-3-2013 06:22:15) deeplink to this comment

Hi,
What VBA coding can i use to change my pivot table data source that is connected to slicers?

My data will keep increasing and i need to update the data source range every time.


my slicers becomes unreadable when i use the following code:

Sheets("Charts").Activate
    ActiveSheet.PivotTables("PivotTable2").SourceData _
    = Sheets("Data").Range("A7").CurrentRegion.Address(True, True, xlR1C1, True)


and my Excel will crash if i use the following code:
    Sheets("Charts").Activate
    ActiveSheet.PivotTables("PivotTable2").SourceData _
    = Sheets("Data").Range("A7").CurrentRegion.Address(True, True, xlR1C1, True)

ActiveWorkbook.SlicerCaches("Slicer_Geographical_Region").PivotTables(1). _
        PivotCache.Refresh