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!

Prev: Synchronising Slicers

Comments

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

Comment by: Sai K Revuru (7-3-2019 12:15:28)

deeplink to this comment

Thank you Jan. I think I will go with getpivotdata. Thank you so much for your help.

Comment by: Cat (18-3-2019 16:47:21)

deeplink to this comment

First, THANK YOU for the function to return Slicer Selections. This code has been ESSENTIAL for a number of projects over the last year or so!

My question now... I have a lengthy staff list and when the user "de-selects" a staff member, I do not want to display the 87 people _selected_ but only indicated which person is NOT selected.

Is there an easy way to do that? and THANK YOU!!!

cat

Comment by: cat (18-3-2019 16:54:25)

deeplink to this comment

Ah! nevermind! I just wrote in asking about NON SELECTED... but then looked more closely at your awesome code and it was trivial to edit! I include here in case you'd want to correct me somehow... :)
THIS IS AWESOME!!!!

Public Function GetNonSelectedSlicerItems(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 = False Then
                GetNonSelectedSlicerItems = GetNonSelectedSlicerItems & oSi.Name & ", "
                lCt = lCt + 1
            End If
        Next
        If Len(GetNonSelectedSlicerItems) > 0 Then
            If lCt = oSc.SlicerItems.Count Then
                GetNonSelectedSlicerItems = "All items omitted"
            Else
                GetNonSelectedSlicerItems = Left(GetNonSelectedSlicerItems, Len(GetNonSelectedSlicerItems) - 2)
            End If
        Else
            GetNonSelectedSlicerItems = "No items omitted"
        End If
    Else
        GetNonSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
    End If
End Function

Comment by: Jun (29-3-2019 03:37:34)

deeplink to this comment

i still cant figure this out... does anyone have the code for the inquiry below

Comment by: Maria (3/31/2015 1:03:16 PM)
deeplink to this comment
How would you modify the vba to display only available items?

For example:

Let's say you have 2 slicers, one CITIES and one SHIP COUNTRY.

If you select "Austria" in SHIP COUNTRY, only a select number of CITIES are now valid as selection in the CITIES slicer.

How do I list those available items?

Thank you.
Maria

Comment by: Jan Karel Pieterse (29-3-2019 14:39:56)

deeplink to this comment

Hi Jun,

Does this post help?

https://jkp-ads.com/articles/slicers05.asp?AllComments=True#23850

Comment by: Jun (3-4-2019 00:57:51)

deeplink to this comment

You Are AWESOME !!!!

Comment by: cat (31-5-2019 22:33:00)

deeplink to this comment

And now... because this UDF is Volatile (and therefore slowing down my workbook), I wanted to make this into a SUB instead of a function... so now I can click a button to run this sub and return the selected items:

Sub ReturnSelectedSlicerItems(SlicerName As String, wsName As String, OutputCell As String)
    Dim oSc As SlicerCache
    Dim oSi As SlicerItem
    Dim lCt As Long
    Dim GetSelectedSlicerItems As String
    On Error Resume Next

    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 selected"
            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

    Worksheets(wsName).Range(OutputCell).Value = GetSelectedSlicerItems

End Sub

Comment by: Jan Karel Pieterse (3-6-2019 09:40:00)

deeplink to this comment

Hi Cat,

I'm not sure why you think the function is volatile, it shouldn't be (there is no Application.Volatile in there).

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.

Subscribe to our mailing list

Showing selected Slicer items on a worksheet

The slicer name

CUBERANKEDMEMBER (for powerpivot slicers)

The UDF

Conclusion

Comments

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