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

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

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

Third party tools

Speed up your file

FastExcel
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 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 125 in total (Show All Comments):

 


Comment by: Robert Ricciardi (2/28/2018 2:54:41 PM)

I'm not sure I can give a sample due to confidentiality involving names and such.

What I can tell you is that there are 4 linked pivot tables from 4 separate data sets (monthly, quarterly, semi-annual, and annual). These are counting entries of a particular test performed in the specified timeframe. Each of these sheets have the names and work locations of individuals in the added columns.

I have added a slicer and reported relationships to the other tables. It is set to control the 4 pivot tables. However when the slicer is clicked for a particular location I see the following:

* With the Field setting showing items with zero data, I get all employees from all locations and only the test count from the selected location.

* With the Field setting NOT showing items with zero data, I only get the employees from the selected location with the count of tests entered.

What I would like to accomplish is to have the show items with zero data, but only from the location selected.

I hope this clears the question a bit.

Thank you for your help and time,
Robert

 


Comment by: Jan Karel Pieterse (3/1/2018 9:16:50 AM)

Hi Robert,

I have a hard time picturing your data but I do suspect what you need is not possible unless you change your data layout to one which has a separate table with people which is joined by adding a relationship.

 


Comment by: Tom T. (3/3/2018 3:34:40 AM)

Good Evening,

I have a Slicer that is a custom Calendar by Week. Works fine. However, for example we have February 2018 data that is “not ready” yet for the end user to view. Since there is data thought in the pivot tables the February 2018 are available for selection by the end user. Is there a way via VBA coding to hide or grey out this selection. For example they could only select the January 2018 slicers. We then would need to modify this code each month.

Thanks in advance !

Tom T.

 


Comment by: Ranga (3/5/2018 8:13:57 AM)

Great solution, however after setting it up to get the results from 12 slicers, it runs extremely slow. Sometimes it's taking over a minute to display the data.

Any way to speed it up?

 


Comment by: Jan Karel Pieterse (3/5/2018 10:12:32 AM)

Hi Ranga,

I know, that is one of the draw-backs of this solution.

 


Comment by: Jan Karel Pieterse (3/5/2018 10:15:31 AM)

Hi Tom,

The way to solve that issue is to make sure the data is filtered from the source of the pivottable I'm afraid.

 


Comment by: Dashaun Huston (4/9/2018 6:06:24 PM)

I copied the original function and it's not working as expected. I traced the code and GetSelectedSlicerItems is always "".

Any Clue?

 


Comment by: Jan Karel Pieterse (4/10/2018 11:29:03 AM)

Hi Dashaun,

Does the code correctly find the slicerCache?
Have you tried the built-in alternative function which only works for powerpivot slicers?

 


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.

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment:

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

I give permission to process this data and display my name and my comment on this website.