Excel Slicer Values Download The Example

Excel Slicer Values Download The Example

You can download the example file using the button at the bottom of the post.

Using the Filter section of the PivotTable Fields dialog doesnt allow you to both filter and report on a field (column).

There is an icon at the top of the slicer that allows you to multi-select without having to use the Ctrl or Shift keys.

See It In Action If youd like to see it in action, play around with the Slicer buttons in the Excel WebApp version below.

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 didnt exist prior to this version.

Background Lets 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 well 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, Ive 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, its 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: Lets 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 weve selected as well.

Great But. Creating A Chart Title Based On Slicer Selection(s) The chart has a nasty title.

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 Thats the trick.

If youre using PowerPivot, you can use a CUBE function to pull the slicer value from the PowerPivot cube.

But if youre not using PowerPivot, youre out of luck.

And if you have no idea what Im talking about.

But dont despair, we can still do this, even without PowerPivot.

While we cant 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 cant use it in the Report Filter as well.

So now what You might think its a little hokey, but the answer is to go back to the PivotTable and add a new column.

Well just call it YearFilter, and that column can have a value of Year (or C2 where the year is in column C) Once weve made that change, we right click the PivotTable, refresh it, and add YearFilter to the Report Field area: Now, heres the rub.

Despite the fact that the data is the same, it wont quite work.

To fix this, delete the Slicer, and re-create it against the YearFilter field instead of the Year field.

Once youve done that, youll notice that toggling the slicer DOES work to change the Report Field: Now, lets 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 charts 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.

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.