Retrieving Selections From A PivotTable Slicer

So you've built a really cool PivotTable, and you hooked up a slicer to allow exploration of the data. And now you want to do something really cool, but you need to make your formula react to the slicer value. Can you do it? Of course you can, but how?

This article will focus on the technique to do exactly that: return the value of a slicer to a formula. Note that, in order to follow along you will need Excel 2010 or higher, as Slicers didn't exist prior to this version.

Background

Let's assume that you have a set of data that looks like this:

Click image for larger version. 

Name:	1.png 
Views:	51312 
Size:	32.1 KB 
ID:	586

Creating the PivotTable

So the first thing we do is add a new PivotTable:

  • Select somewhere in the data table
  • Choose Insert-->PivotTable
  • Place it on a new sheet and configure it as follows:
Click image for larger version. 

Name:	2.png 
Views:	50961 
Size:	39.2 KB 
ID:	587

Now, notice that we have a PivotTable that shows the total invoices broken down by year and month.

Adding a PivotChart

Next we'll do is add a PivotChart to this data. To do this we need to:

  • Click somewhere in the PivotTable
  • Go to PivotTable Tools-->Options-->PivotChart
  • Choose the first line chart and click OK

And that will give us a chart like this:

Click image for larger version. 

Name:	3.png 
Views:	50912 
Size:	23.8 KB 
ID:	588

Now, I've never been a fan of the buttons showing on the chart, so I kill those off by going to PivotChart Tools-->Analyze-->Field Buttons-->Hide All. This gives us a chart that is a little less cluttered:

Click image for larger version. 

Name:	4.png 
Views:	50877 
Size:	23.1 KB 
ID:	589

Adding A Slicer

Now, it's time to add a slicer to give us some drilldown control into the PivotChart. To do that:

  • Click in the PivotTable
  • Go to PivotTable Tools-->Options-->Insert Slicer
  • Choose "Year" from the listed fields and click OK

And now we have a slicer like this:

Click image for larger version. 

Name:	5.png 
Views:	50844 
Size:	4.7 KB 
ID:	590

Let's clean that up a bit as well...

  • Resize the slicer a bit
  • Click SlicerTools-->Options-->Slicer Settings (small button at far left of tab)
  • Set the "Caption" to "Years To Include" and click OK
  • Change "Columns" (right side of tab) to 4

Much better!

Click image for larger version. 

Name:	6.png 
Views:	50839 
Size:	3.8 KB 
ID:	591

And if we click the different slicer buttons we can see that the chart filters down the data that we've selected as well. Great! But...

Creating A Chart Title Based On Slicer Selection(s)

The chart has a nasty title. All it says is "Total". So how do we fix that?

Well, we know that we can link a chart (or PivotChart) title to a cell with a formula in it, but how do we get the value of the slicer into the cell? That's the trick...

If you're using PowerPivot, you can use a CUBE function to pull the slicer value from the PowerPivot cube. But if you're not using PowerPivot, you're out of luck. (And if you have no idea what I'm talking about... well... you're in the "out of luck" camp!) But don't despair, we can still do this, even without PowerPivot.

While we can't use a formula to pull the value directly from the slicer, we CAN pull a value from a PivotTable Report field with a formula. So all we need to do is drop the "Year" field in the Report Filter area of the PivotTable, right?

Hang on a second, we have a problem. Because Year is already in the PivotTable, we can't use it in the Report Filter as well. So now what?

You might think it's a little hokey, but the answer is to go back to the PivotTable and add a new column. We'll just call it "Year_Filter", and that column can have a value of =[@Year] (or =C2 where the year is in column C)

Once we've made that change, we right click the PivotTable, refresh it, and add Year_Filter to the Report Field area:

Click image for larger version. 

Name:	7.png 
Views:	50984 
Size:	42.4 KB 
ID:	592

Now, here's the rub... the slicers still work, but they don't filter the "Year_Filter" field, they actually filter the Year field in the PivotTable. Despite the fact that the data is the same, it won't quite work.

To fix this, delete the Slicer, and re-create it against the "Year_Filter" field instead of the "Year" field. Once you've done that, you'll notice that toggling the slicer DOES work to change the Report Field:

Click image for larger version. 

Name:	8.png 
Views:	50978 
Size:	46.0 KB 
ID:	593

Now, let's get that slicer value. In cell A2, enter the following formula:
="Total Invoices for "&IF(B3="(All)","all years",IF(B3="(Multiple Items)","selected periods",B3))

This formula will return the year when one year is selected, "all years" if the filter is cleared, and "selected periods" if more than one item, but not all are selected.

Update The Chart Title

Finally:

  • Click on the chart's title ("Total")
  • Press =
  • Click cell A2 and press Enter

And there you go!

Click image for larger version. 

Name:	9.png 
Views:	51016 
Size:	44.9 KB 
ID:	594

You can now hide the row containing the filter if you like, and no one will even realized you used it.

So there you have it. To recap, the big secret to extracting the value from a slicer is to create a Report Filter that matches the Slicer exactly. Once you do this, the Slicer will update the Report Filter, and you can pull the value of the Report Filter using a formula.

See It In Action

If you'd like to see it in action, play around with the Slicer buttons in the Excel WebApp version below. Yes, they're clickable!

Download Completed File

Share:

Facebook
Twitter
LinkedIn

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts