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:

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:

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:

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:

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:

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!

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:

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:

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!

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