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":
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.
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:
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:
with
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.