Showing selected Slicer items on a worksheet

Pages in this article

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.

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

Comments

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

Comment by: Andy Murdoch (13-12-2021 12:02:00) deeplink to this comment

Thank you for this function. I would like to know how to do the opposite of what's being achieved. How do I change it to show the items that have been filtered out?

Comment by: Jan Karel Pieterse (13-12-2021 13:17:00) deeplink to this comment

Hi Andy,

Basically you replace this line:

If oSi.Selected Then

with

If oSi.Selected = False Then

Comment by: Morgan (23-12-2021 17:07:00) deeplink to this comment

I have used this solution for several years in a file that I keep, but this week I started to get some issues and was hoping you could help.

I have the formula referencing my Slicer name
=GetSelectedSlicerItems("Slicer_Fund_Name) but when I enable the document, I get an error in the VBA debugger that says :

Compile Error: Cant Find Project or Library and its highlighting the Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2) section.

Any suggestions?

Comment by: Jan Karel Pieterse (23-12-2021 17:41:00) deeplink to this comment

Hi Morgan,

When you get that error, click OK. Then click Tools, References. Are any marked as MISSING?

Comment by: Markus (28-1-2022 19:53:00) deeplink to this comment

This is a very nice solution!
Two questions...

1.) Is it possible to get the filter / chosen duration of these time span slicers too? E.g. Jan2021 to Aug2021

2.) Is it possible to get the results in individual rows, e.g. by using that function as a kind of array formula or any other kind? So to see the filter values not comma separated but each in a single line?

Comment by: Jan Karel Pieterse (31-1-2022 10:19:00) deeplink to this comment

Hi Markus,

Don't know if a timeline slicer allows us to access it's data. Perhaps you can try?

In the comments you can find several examples of UDFs returning an array, for example:

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

Comment by: Judy (9-4-2022 02:57:00) deeplink to this comment

I am trying to find a way to limit a slicer to single select. I need to Disable the multi select and CTRL button so the users cannot circumvent the Single Select as the ONLY select.
I have a Data Entity that needs the single select and it filters 3 other slicers in turn. Is there any way to limit the slicer as a single select, or maybe use a single select drop down that will filter the slicers? With or without VB code.

Thanks in advance...

Comment by: Jan Karel Pieterse (11-4-2022 10:22:00) deeplink to this comment

Hi Judy,

There is no way to limit a slicer to just one item, But what you can do is this:

- Add a pivottable to the same slicer in some out-of-the-way location which has the slicer field as a row item
- Write a formula to count the # of filtered rows
- Use a formula in a cell next to the slicer that displays red text as soon as more than one item is selected in the slicer.

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.