Most Valuable Professional

View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.


Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Pivottable Slicers > Show Selected Items

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 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
    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
        If Len(GetSelectedSlicerItems) > 0 Then
            If lCt = oSc.SlicerItems.Count Then
                GetSelectedSlicerItems = "All Items"
                GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)
            End If
            GetSelectedSlicerItems = "No items selected"
        End If
        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:



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!




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


Comment by: Alex (9/11/2017 4:22:19 PM)


This code works Great, thank-you! In my slicer I'm filtering Dates, If I wanted the cell result to show a date range instead of each date (ie. 1-Jan-17,2-Jan-17,3-Jan-17) would show as "1-Jan-17 thru 3-Jan-17" would that be possible?


Comment by: Jan Karel Pieterse (9/11/2017 5:13:32 PM)

Hi Alex,

One way I can get Excel to do that is by actually grouping the dates on a fixed number of days.
Another would be to add a column which calculates the "period" from the date column and use that for the slicer.


Comment by: Lucas Coy (11/20/2017 6:42:50 AM)

Hi Jan,

First of all, your code works fantastic! I do have a question though, is it possible to make it work on a "timeline" slicer? I've used your UDF on the timeline that I have in my sheet but it always returns "No Items Selected" even though it does have items selected. So I'm not sure if it's something I'm doing wrong or what.



Comment by: helen (12/15/2017 10:49:22 AM)


I am trying to display an item that when clicked in my slicer it display it in another cell.

I want to be able to do an if statement that so if certain things are selected then in another box then a message will display.

thanks for your help.


Comment by: Jan Karel Pieterse (12/15/2017 1:52:56 PM)

Hi Helen,

Can you elaborate? This page already shows how to get the selected items from a slicer?


Comment by: Kamil (1/3/2018 10:06:38 AM)

Great solution, much appreciated, made my work much easier, thanks a mile!


Comment by: destinationsky (1/10/2018 12:43:09 PM)

Hi, awesome post!

I might have missed it but I´m trying to "substitute/control" a slicer with a dropdown. I am working with many pivots that I´d like to filter via a few dropdown´s as multiple slicer´s would be too cumbersome for my use.

Any help would be greatly appreciated!



Comment by: Jan Karel Pieterse (1/10/2018 2:15:20 PM)

Hi destinationsky,

A drop-down only allows selection of one item, as I'm sure you are aware of. You could attach a macro to the drop-down, or if you use data validation in a cell (preferred), you could use the worksheet_Change event to trigger a macro that filters the proper field of the pivottable using the content of the cell or drop down. If you then add slicers tied to the same field, those slicers will ensure other pivottables will "receive" the same filter.


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:

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].