Did you know you can use Slicers to filter Excel Tables? It’s like having your cake and eating it too! Not just any cake either, it’s like the most delicious Excel cake you’ve ever eaten and once you get a taste of the ‘Table Slicer Cake’ you’ll be wanting more. The good news is it’s zero calorie 😉
Slicers enable you to quickly and easily toggle filters on and off. I like to use them to save time applying filters I use regularly.
Below is an example of some English Premier League Football (soccer) data with Slicers to filter by Home Team or Away Team.
The great thing is you can insert a Slicer for any column(s) in your Table.
In order to use Slicers like this you first have to format your data in an Excel Table, and then you can insert a Slicer.
Note: Slicers are only available in Excel 2010 or later and unfortunately only work with Tables in Excel 2013, but don't let that stop you cause you can still take advantage of Tables and use Slicers with PivotTables in Excel 2010.
Enter your email address below to download the sample workbook.
Master Excel Tables
Tables have other benefits that make them a key ingredient for your Excel workbooks. If you want to master Excel Tables (and you should), get a copy of this book by Excel MVP’s Zack Barresse and Kevin Jones. It’s a complete guide to Excel Tables and contains everything you’ll ever want to know, including automating Tables with VBA, using them with SharePoint, and Tables on the iPad, Mac and Office Mobile.
Please Share
If you're already using Tables and Slicers then you know how good they are but less than 1% of Excel users have discovered these tools so please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.
Disclosure; I'm an affiliate for Zack and Kevin's book so if you click on the link above and buy a copy I'll make a couple of dollars. I'd recommend their book even if I didn't earn anything from it, in fact if you prefer you can simply search for their book online without clicking my link and buy it and I won't earn anything. I hope that shows you just how important I think learning Excel Tables' is.
Stephen says
Tables and slicers are a great way to produce a dynamic chart, but how do you find out the slicer selection to use it in a dynamic chart title?
Mynda Treacy says
Hi Stephen,
If you’re confident that there will only ever be one item selected in the Slicer at a time then you can use this array formula to return the first visible cell in the column of the filtered table:
Remember to enter this with CTRL+SHIFT+ENTER as it’s an array formula.
Mynda
Rita says
Dear Mynda,
I have an Excel Table and I have a Pivot Table with the Excel Table as data source. Is there any way to use a slicer to control both the Excel and the Pivot table with the same slicer?
Thank you!
Mynda Treacy says
Hi Rita,
No, they’re not able to be connected. The PivotTable slicer references data in the Pivot Cache, whereas the Table Slicer references the Table directly, so one Slicer cannot control both….unless you use some custom VBA to mimic the selections from one Slicer to the other.
Mynda
Rita Kiss says
Thank you! It’s a pity but I hope I can find a workaround.
JoAnn Paules says
There may be a workaround depending on how you have your data laid out and what you need to see.
I track metrics for one of my manager’s direct reports. I have a table of data that I need to use for charts tracking several employees effectivity rate per month. And to keep it interesting, he wants the chart to show a rolling 13-month time frame.
I have 6 regular tables (one per employee and one for an overall percentage) all lined up side by side. I have a table slicer for the first table. When I select the 13-months I want to track, all 6 of my tables are filtered – because they are side my side. I added a pivot table (using months as the row label) in beside the tables and when I filtered my tables, it too was filtered.
I tried inserting a pivot chart thinking that would also change but it doesn’t. Apparently pivot charts are smart enough to know the difference between not being able to be seen and filtered. Darn it.
Mynda Treacy says
Clever twist, JoAnn 🙂
JoAnn Paules says
There is a little bit of confusion in this post. Slicers became available for pivot tables in Excel 2010, however, they can only be used in tables with Excel 2013. Much to my disappointment. I love slicers.
Mynda Treacy says
🙁 sorry about that JoAnn. I’ve updated the ‘note’ to include this. Pray for Excel 2013…or wait for Excel 2016 which will be out this year!