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
The Data
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.
However, with this workaround we can use Slicers in formulas thus enabling us to summarise data spread over two or more tables of data, which would otherwise require Power Pivot and or Power Query.
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.
Please Share
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.
Ron says
Hi,
Great tutorial, which will help me solve my excel issues. It will help me huge, when the missing graphics will be shown. Are you maby able to fix them?
Philip Treacy says
Thanks Ron.
Sorry about the images, should be all displaying now.
Di says
Interesting! How do you handle it when you don’t pick any category but your filter is cleared out (All)?
Thank you!
Mynda Treacy says
Hi Di,
That’s similar to selecting multiple items in the Slicer i.e. more than one item is selected and the SUMIFS formula can’t handle multiple criteria for the same criteria range.
In that case you can use the SUMPRODUCT function with an OR operator between each criteria to accommodate multiple items selected, or if you just want to handle one selection or no selections, then you could wrap your SUMIF/S in an IF function that checks to see if there are no selections made, in which case it would just SUM the range, otherwise use SUMIF/S to accommodate the selection in the Slicer.
If you have a lot of items in your Slicer then trying to accommodate every option will make for a very long SUMPRODUCT formula. In that case you might be better off trying to work with PivotCharts, or build a dynamic named range that references the PivotTable range and use that as your chart source range for a regular chart.
Mynda
Marcos Zattar says
This is great, Mynda, thanks!
How do you manage to get the chart to change smoothly?
Cheers,
Marcos
Mynda Treacy says
Hi Marcos,
That’s a built in feature available in Excel 2013 onwards.
Mynda
Jeff Baisch says
I can now understand how to use this useful tool. After converting one of my reports that consists of 20 different tabs based on inventories down to 3 tabs I was very happy. However, I emailed the report out to all the recipients, come to find that Slicers are not usable on smartphones. I open the spreadsheet on my Android and get the message:
“Slicers aren’t fully displayed or interactive. If you data was previously filtered, it’s shown in its filtered state.”
Is there any way to use spreadsheets with slicers on smartphones?
L.E.: We are using Excel 2010 to create the files.
Thanks
Jeff
Catalin Bombea says
Hi Jeff,
Even if the slicers are created with Excel 2010, to view them and use them you have to use Excel 2010 or higher versions, or with Excel Online.
You can upload them on OneDrive, and view them with a browser, with Excel Online, instead of using the software installed on your smartphone.
Catalin
Mynda Treacy says
It will depend on your phone. On my phone opening a file containing Slicers in Excel Online in Firefox doesn’t display the Slicer at all 🙁 My phone is quite old though!
Catalin Bombea says
Mine still has keyboard, it’s not smart 🙂 , I cannot test on it…
Mynda Treacy says
😀 now I don’t feel so bad about having a 4 year old phone!
Godfred Owusu Adjei says
Nice tutorial,I will appreciate if you can share with me the video tutorials.
Thanks
Mynda Treacy says
Glad you liked it, Godfred.
There’s no video for this tutorial.
Mynda
Ken Witchel says
Mynda, thank you for the very interesting post. I hope to implement the slicer instead of using a dropdown. It is possible to use the slicer selection to automatically expand when more data is added to the table? For example, if I created the original slicer to select Year when the table currently had data for the years 2010 to 2015. Now that I am adding 2016 data, could “2016” be automatically added to the slicer selections? Or would the original pivot table of Years first have to be refreshed?
Mynda Treacy says
Hi Ken,
You have to refresh the PivotTable for the Slicer to pick up the new year, but then you’d want to refresh the PivotTable if you added new data to the source anyway.
You can set the PivotTable to automatically refresh upon opening of the workbook, or with some VBA you can set the PivotTable to refresh upon change of worksheet selection which I teach in my Excel Dashboard course.
Kind regards,
Mynda
DIEN says
Could you create more the option to choose multiple category? For example I want to show the chart which compare actual vs budget for 02 products.Or show the chart of top 3 actual vs budget
Mynda Treacy says
Hi Dien,
It’d be easier to consolidate the data with Power Query or Power Pivot and insert a Pivot Chart than try and write a formula to handle a variable number of items selected in the Slicer.
Mynda
Michael May says
I have a PT on which I have multiple slicers that can be applied so would the Power Pivot be a better option in order to ensure that the unsliced total of the PT (when slicers are applied) always equals the total of the source data?
Thanks
Mynda Treacy says
Hi Michael,
Power Pivot would be the only way to do this.
Mynda
Paula says
Great post – very creative! I tried this with a slicer on the table and don’t believe this works. When a table is sliced, it filters the list and the data stays in its original cell, so naming the first cell in the table list does not return the desired result.
Mynda Treacy says
Good point, Paula. We could probably write a formula to extract the visible cell, but it’s easier just to use the PivotTable method.
T N Ramanathan says
Excellent Artcle.
Philip Treacy says
Thanks, glad you liked it 🙂
Phil
Anika says
Brilliant and very useful in a lot of situations. Thank you
Mynda Treacy says
Thanks, Anika. Glad you’ll be able to make use of it.
Mynda
Ellen says
This is something that will help me tremendously! One question though, what if my data is not broken down by month but rather by day?
Mynda Treacy says
Hi Ellen,
I’m glad you’ll find this useful. My SUMIFS formula assumes the data is broken down by day. I just happen to only have one day date for every month.
Mynda