Showing selected Slicer items on a worksheet

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 pivottable is built using a powerpivot model, then you can access this information by using the CUBERANKEDMEMBER worksheet function. If however the slicer was built off of a "normal" pivottable, 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 userinterface 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 powerpivot 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 (note the formula has ; as the delimiter, many users need to use the comma instead).

Using the CUBERANKEDMEMBER function

Note, that when less items are selected than the number of cells the function was entered into, the excess cells will show #N/A.

These #N/A's can be hidden with:

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

or counted with

=IF(ROW()-ROW($G$5)<=CUBESETCOUNT(CUBESET("PowerPivot Data",Slicer_Name)),CUBERANKEDMEMBER("PowerPivot Data",Slicer_Name,ROW()-ROW($G$5)),"")

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 slicername 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 2010 and 2013. A great addition to the product!

Comments

Showing last 8 comments of 190 in total (Show All Comments):

Comment by: Jan Karel Pieterse (23-2-2021 10:16:00) deeplink to this comment

Hi Andreas,

I think you want the slicer to turn off its filter when the selection changes of another slicer? So for example: if the another country is chosen, the filter from cities is removed?

Comment by: Jan Karel Pieterse (23-2-2021 10:25:00) deeplink to this comment

Hi Ranga,

If you expand the page to display all comments:

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

You'll see there are quite a few iterations of the function for certain situations. I suggest you scroll through them and try a couple of variations.

Comment by: Andreas (23-2-2021 10:34:00) deeplink to this comment

Hi Jan,

that is not the problem. I wanted to select the displayed items automatically, if the filter changed. But the good slicers in Excel are, the bad are the possibilites in VBA. I solved this another way with a PivotTableUpdate event.

Thanks

Comment by: Jan Karel Pieterse (23-2-2021 11:13:00) deeplink to this comment

Hi Andreas,

I'm not sure what it is you wanted, but if you've solved it there isn't much point for me to try to understand is there :-)

Comment by: Alexius Galloway (18-3-2021 21:13:00) deeplink to this comment

I use and love this function, thank you; however, I would like to know how to custom sort when multiple items are selected.

For my current situation, user will select between 1 and 7 items. Other tables may be between 1 and 4, 1 and 15 items...but again, for today 1 and 7 items.

Comment by: Jan Karel Pieterse (19-3-2021 13:58:00) deeplink to this comment

Hi Alexius,

What exactly do you have in mind?

Comment by: Felipe Mondaca (20-4-2021 15:17:00) deeplink to this comment

Hi, I'm a trainee student of Chem. Engineering, I've never thought I was going to need this kind of VBA. I found the link to this topic in a Youtube comment, and I'm so glad for that.
You helped so much to me with this, and I just want to say thank you so much.

Comment by: Jan Karel Pieterse (20-4-2021 17:30:00) deeplink to this comment

Hi Felipe,

I'm glad I was able to help you!

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.