Slicers are a great tool for incorporating interactivity into your reports but we don’t always want to analyse our data with PivotTables and PivotCharts. Nor is our data always in the perfect format for PivotTables.
So, let’s look at how we can use the Excel Slicer Selection in formulas which will enable us to create interactive reports that use regular charts with Slicers, like the one below:
Note: Slicers are available in Excel 2010 onwards.
Download the Workbook
For this example my data is spread over two sheets, each in an Excel Table. The Tables have Names; Actual and Budget. You’ll see these names in the Structured References in my formula later.
Insert a Quasi PivotTable
Since Slicers are connected to PivotTables the first step is to insert what I call a quasi PivotTable, which I've linked to my "Actual" data Table. This is a PivotTable that only has row labels for the items I want displayed in my Slicer.
For example, I want a Slicer for my Category field so I’ve created this quasi PivotTable in cells L4:L12:
Notice how it has no Values field, only the Category field in the Rows area.
Tip: You can put the field in the columns or filters area instead. It doesn’t really matter. I like to use the Rows area so that if multiple items are selected in the Slicer then they will fill down a column as opposed to across a row, or displaying the text ‘Multiple Items’ in the Filters area.
Insert a Slicer
Next I need to insert a Slicer for the PivotTable Category field.
It’s dead easy; with a cell selected in your PivotTable go to the Insert tab > Slicer. In the dialog box (image below), select the field you want to insert a Slicer for:
I’ll also change my Slicer caption to say "Select One" to give some guidance to the user. To modify the caption: right-click the Slicer > Slicer Settings, type in a new caption and click OK:
Tip: The reason I only want one item selected in the Slicer is because I’m going to use the SUMIFS function to summarise my data and I’ll be using the selection in the Slicer as one of the criteria. Remember, SUMIFS treats each criteria as AND, so it cannot handle more than one category. For that you would have to use the SUMPRODUCT Function.
When you select an item in the Slicer the row labels are filtered to only display the selected item(s), which can be seen in cell L5 below:
You can see in the name box in the image above that I’ve given cell L5 the Name "slicer_selection", which I will use in my SUMIFS formula.
Use Excel Slicer Selection in Formulas
The table that feeds my chart is in cells H4:J16:
The SUMIFS formula in cell I5 is:
=SUMIFS(Actual[Actual],Actual[Category],slicer_selection, Actual[Month],">="&Report!H5, Actual[Month],"<="&EOMONTH(H5,0))
Notice how the third argument references “slicer_selection” which is the name I gave cell L5. It’s as simple as that, I’m just referencing the row label in the PivotTable that displays the item selected in the Slicer. The SUMIFS formula automatically updates to reflect any changes in the Slicer selection.
Here is the English translation for my SUMIFS formula:
SUM the Actual column of the Actual Table IF the Category in the Actual Table Category column is the same as the Category in the slicer_selection cell, AND the date in the Month column of the Actual Table is greater than or equal to the date in cell H5, AND the date in the Month column of the Actual Table is less than or equal to the end of the month (EOMONTH) date in cell H5.
The limitation is that SUMIFS can only handle one selection in the Slicer. If more than one item is selected then only the first item is reflected in the chart.
Dynamic Chart Label – The Icing on Top
Notice how the title in my chart reflects the selection in the Slicer?
I’ve linked my chart title to an IF formula in cell N4:
It picks up the Category selected in the Slicer from the PivotTable cell L5 called "slicer_selection" and concatenates the text “Actual vs Budget Sales”, and if more than one Category has been selected it displays this message in the chart title:
“Please Choose Only One Category”
Benefits of Using Slicer Selection in Formulas
Obviously Slicers were designed to work with PivotTables and PivotCharts, and if you have Excel 2013 onwards then you can also use Slicers with Excel Tables.
This method also enables us to use regular charts which are more flexible than PivotCharts.
Final word: In the Slicer settings you may have noticed a "name to use in formulas" (see image below). This is only for use with Power Pivot models and requires the CUBERANKEDMEMBER function or VBA, but that’s a post for another day.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.