How to get selected items in a slicer without VBA

Slicers is an essential tool in Excel when working with power pivot pivot tables (and “normal/old” pivot tables). But when connecting the same slicers to multiple pivot tables and perhaps place all your slicers on a separate sheet you have to be able to show what your model users have selected in the slicers when they look at the pivot table.

You can create VBA code to read the selected items from the slicer object, but this code cannot be run if you publish your model for use in Excel Web App on SharePoint.

So to avoid code I have created formula to generate a text string indicated what the user selected in a slicer.

Step 1 Create the data for a powerpivot model

 

First I create some sample data to use in a powerpivot model.

The Store =INT(RANDBETWEEN(1;50))

The date =TODAY()-RANDBETWEEN(1;100)

The Product =RANDBETWEEN(1;10)

The Sales Value =RANDBETWEEN(100;4000)

And then copy these formulas to generate a random number of rows.

Step 2 Create a linked table to PowerPivot

 

Select PowerPivot and click Create Linked Table

This will link the data to PowerPivot so we can build a simple model.

In order to create a year and month slicer – I add to calculated columns.

Year: =Year([Date])

Month: =Month[Date])

Step 3 Create a pivottable with slicers

 

Then I setup a pivot table with slicers that might look like this.

 

Step 4 Let’s calculate the selected items in the Store Slicer

 

A slicer in Excel has some settings in which you can identify the slicer in your formulas.

 

In this case our slicer “Store” is called “Slicer_Store”.

We can use this name in our formulas.

In order to get the number of selected items in a slicer we use the function – CUBESETCOUNT

 

The formula =CUBESETCOUNT(Slicer_Store) will return 1 – as all items is selected

 

 

When you select three items it will return three.

Next function to use is CUBERANKEDMEMBER-

 

CUBERANKEDMEMBER can return a member in a set by specifying it rank in the set.

Using the name of the slicer – the name will return a set of selected items in slicer !!!!!!!!

=CUBERANKEDMEMBER(“PowerPivot Data”;Slicer_Store;1)

So this formula will return the first selected item in the slicer and if all items is selected it will return All.

 

Step 5 Lets create a label for the selected items

 

A slicer can contain many items and I don’t want to create a very long label, I chose to let the label contain the first 10 selected items and if more than 10 is selected – the label should return the text “More than 10 selected”

 

In column M there is a rank number from 1 to 10.

In column N next to the rank I create a formula that retrieves the ranked member in the slicer.

=IF(M14<=$N$12;CUBERANKEDMEMBER(“PowerPivot Data”;Slicer_Store;M14);””)

The formula test if the rank is lower or equal to the number of selected items in the slicer (calculated with CUBESETCOUNT), if it is – it will return the ranked member in the Set – otherwise it will return blank text.

In column O I create formulas to generate the label.

First formula just takes the first member

=N14

Second item

=O14&IF(N15<>””;”, “&N15;””)

Tests whether the N columns returns an result – if it does it will concatenate the two labels split by a comma.

This formula is copied down for all rank numbers.

And finally a formula to create the complete list or if more than 10 is selected

=IF(N12>10;”More than 10 selected”;O23)

The cell is then named “StoreSelected” and then you can refer via the name on any of your sheets in the workbook.

Try it

 

You can download the example here – Link (open the file in Excel – not in Excel web app as powerpivot isn’t supported )