Back in June, Kasper posted a trick which lets you detect a user’s selection in a slicer, and use that in a PowerPivot measure. That’s a very useful trick, one that we employ all the time at Pivotstream.
But sometimes, that is overkill. Sometimes, you just want to grab a slicer’s selected value and use it in an Excel formula, right there in the sheet. Here is the simplest method we have discovered so far:
1) Duplicate the field as a slicer AND a report filter
First step is to take the field you want to use as a slicer, and add it to your pivot both as a slicer, and as a report filter, as in this simple pivot:
Date Field Dragged to Both Slicer and Report Filter
2) Observe that the Report Filter “Tracks” the Slicer
OK, now click a date in that slicer. Look what happens to the report filter in the sheet:
Cool huh? Since they are the same field, the report filter has to always be in synch with the slicer. And unlike the performance penalty that can pile up with multiple slicers, duplicating a field like this will NOT make your pivot slower at all.
3) Use the Report Filter Cell in a Formula
Yeah, you probably see where this is going already: now you can reference the report filter cell in a formula, like this:
4) Clean up the visuals
Move the formula to a more centered location, change the font, and hide the row that contains the report filter:
Notes
-
I haven’t tried it but I am pretty sure this will work with regular pivots, too, not just PowerPivots.
-
If you select multiple items in the slicer, you will get the text “Multiple Items” in your formula instead of a single value. UPDATE: I have since written another post that covers this. It’s not a pretty technique but it gets the job done. Post is here: http://www.powerpivotpro.com/2011/09/catching-multiple-slicer-selections-in-a-formula/ (November 2012 update: see the link below for a less clumsy method).
-
When you really get started thinking about this, there’s really no limit to the cool tricks you can pull off. I’ll show a few more specific examples over time, but I’m sure you guys will discover many cool tricks of your own, too. Here’s some food for thought: report filters aren’t the only way to get slicer selections into a worksheet cell.
Update, November 2012
I’ve since posted yet another way to do this that is probably the simplest I’ve personally used to date, and it handles multi select quite well too:
http://www.powerpivotpro.com/2012/11/better-way-to-catch-multiple-slicer-selections-in-a-formula/
I was playing around with slicers in Excel 2010 because I needed to retrieve some values from them.
I had a slicer with a date in string format like yyyymmdd called Slicer_TIMEID
to count the number of selected items I used :
=CUBESETCOUNT(CUBESET(“PowerPivot Data”,Slicer_TIMEID,”Default Caption”",4))
to get the latest item I used :
=CUBERANKEDMEMBER(“PowerPivot Data”,CUBESET(“PowerPivot Data”,Slicer_TIMEID,”Default Caption”,4),1)
to get the first item I used :
=CUBERANKEDMEMBER(“PowerPivot Data”,CUBESET(“PowerPivot Data”,Slicer_TIMEID,”Default Caption”,3),1)
to get the latest 3 items I used an array formula :
=CUBERANKEDMEMBER(“PowerPivot Data”,CUBESET(“PowerPivot Data”,Slicer_TIMEID,”Default Caption”,4),{1,2,3})
this only worked horizontally ( don’t ask me why )
to get this one to work vertically just use TRANSPOSE
I hope this will be of any use to somebody
Very nice!
To make the array dynamic, use (from another slicer example):
=CUBERANKEDMEMBER(“Sandbox”,CUBESET(“Sandbox”,Slicer_Color,”Color”,3),ROW(INDIRECT(“1:”&CUBESETCOUNT(CUBESET(“Sandbox”,Slicer_Color,”Color”,4)))))
Yes indeed this does work with other pivot tables! Specifically I use this techique with pivot tables against SSAS Cubes, and it is very effective.
How can we use this technique when multiple values are selected? I get the text “Multiple items” and the formula errors out.
I’d really like the answer to Push’s last question if anyone comes up with a way. I came looking to solve this question, but alas.
Your comment today got me thinking. I emailed you a doc Joe that lays out one way this can be done. I’ll probably do a post on it too.
I would like to learn how to retrieve the values of Multiple items as well
OK, I have added this to my list of upcoming blog topics
I am also interested on how to retrieve the ‘Mutiple items’
Multiple slicer items is now covered in this post:
http://www.powerpivotpro.com/2011/09/catching-multiple-slicer-selections-in-a-formula/