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

 


Comment by: Ashley (9/27/2016 10:32:53 AM)

Hi Jan,

I'll have to create an example file as the data is confidential. How would I share this with you?

Thanks,
Ashley

 


Comment by: Jan Karel Pieterse (9/27/2016 11:20:53 AM)

Hi Ashley,

You can send it to as a reply to the confirmation message you got stating your reply was approved.

 


Comment by: Leo Meijer (11/4/2016 8:56:46 AM)

Hello all

In a PivotTable, you can click after filtering the filtered items, allowing you to see the underlying data will take on a new tab.

Now I use multiple slices to filter a PivotTable.
But I do not get the "slicers with filtered data" to see a new tab.

Does anyone have an idea how I wanted to see the filtered data or receive?

Thank you very much

Leo Meijer

 


Comment by: Jan Karel Pieterse (11/14/2016 4:30:39 PM)

Hi Leo,

I assume your problem is that the data that gets dumped on the new tab is not filtered according to your slicer filters, correct? This happens to fields with slicers that are NOT a field in the pivottable. TO ensure you get filtered drill-down results, make sure your slicer fields are all in the pivottable (add them as page fields for instance).

 


Comment by: Paul Bell (11/24/2016 3:47:18 PM)

Hi,

I have a workbook with 3 sheets, "Complaint Chart", "Pivot Tables" and "Pivot Charts".

The actual pivot tables, on the sheet Pivot Tables, are created from a dynamic named range from the Complaint Chart sheet.

I then have 3 slicers, Year, Day and Type on the Pivot Chart sheet which are linked to all my pivot tables.

The problem I have is when the userform adds new data to the Complaint Chart sheet the pivot tables refresh ok but the slicers appear to duplicate the 'filter options'. For example, The complaint chart sheet contains many rows containing the year 2016, however when new data is added to the complaint chart, with year 2016, the pivot tables refresh ok but the slicers then show 2016 twice in the 'filter options'.

I cant figure out why this is happening or how to stop it, please can anyone help?

Thanks in advance. Paul.

 


Comment by: Jan Karel Pieterse (11/25/2016 2:33:22 PM)

Hi Paul,

I strongly suspect there are both 2016 viewed by Excel as numbers and 2016 viewed by Excel as text.

 


Comment by: Joey (11/30/2016 4:41:52 PM)

Hi, I have a pivot table with seven slicers and I used the UDF and it worked perfectly for six of the slicers. However, for the 7th one it always says "No items selected" regardless of if I have items selected or not.

Not sure if this matters or not, but that slicer has a lot of items in it (over 10,000).

Does anyone know why this is happening?

Thanks.

 


Comment by: Jan Karel Pieterse (12/6/2016 10:08:37 AM)

Hi Joey,

I don't know. Perhaps that slicer has other differences?

 


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 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].