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


    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


    • 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


    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!


    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

    Comments 2 Comments
    1. Chezzer14's Avatar
      Chezzer14 -
      I found this tip very useful. Thanks! Is there a way to also retrieve the selected field names from the Column Labels section and Row Labels section of the Field List to also embed in a chart title like this?
    1. Ken Puls's Avatar
      Ken Puls -
      Hi there,

      Sure, why not? You can reference any cell in the PivotTable using a formula. Chances are pretty good that the default will come up as a GETPIVOTDATA function, which could be what you what (they will dynamically reference cells even when they move), or you can fall back to A1 style referencing as well. Use that formula in a cell and then link it to the chart title.