Slicers for Excel Tables

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.

Slicer filtering an Excel Table

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.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Download the Excel Workbook and Slice some Tables yourself. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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), then consider my comprehensive Excel Tables course that'll have you up and running with them in under 1 hour.

Or, 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.

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.

Comments

Stephen

September 13, 2017 at 7:26 am

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
  
  September 13, 2017 at 9:25 am
  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:
```
=INDEX(B2:B200,MIN(IF(SUBTOTAL(3,OFFSET(B2,ROW(B2:B200)-ROW(B2),0)),ROW(B2:B200)-ROW(B2)+1)))
```
  Remember to enter this with CTRL+SHIFT+ENTER as it’s an array formula.
  
  Mynda
Rita

June 18, 2015 at 9:33 pm

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
  
  June 18, 2015 at 10:35 pm
  
  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
    
    June 23, 2015 at 2:55 am
    
    Thank you! It’s a pity but I hope I can find a workaround.
    
    - JoAnn Paules
      
      June 24, 2015 at 8:43 pm
      
      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
        
        June 24, 2015 at 8:58 pm
        
        Clever twist, JoAnn 🙂
JoAnn Paules

February 10, 2015 at 9:27 pm

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
  
  February 10, 2015 at 9:47 pm
  
  🙁 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!

Master Excel Tables

More Excel Posts

Reader Interactions

Comments

Leave a Reply Cancel reply