Adding Slicer Filters to Tables in Excel
Tables were added to the 2007 release of Excel and provide exceptional functionality as automatically expanding and contracting ranges of data. However, you can increase the usefulness of tables significantly if you add Slicer filters to your tables so that you can filter table data more quickly and easily. In this tip, you will learn how to add these filters to your tables.
How to Add Slicers to Your Tables
Slicer filters appeared with the 2010 release of Excel and at that time, you could only use them to filter data in PivotTables. However, beginning with the 2013 release of Excel, you can use Slicers also to filter data in tables. Notably, these filters do not add any additional filtering capabilities above and beyond those found in the traditional drop-down filters; rather, they make it easier and faster to apply filters to tables (and PivotTables and PivotCharts.)
To add a Slicer as a filter to a table, simply click on the table and choose Insert Slicer from the Table Tools Design tab of the Ribbon. Next, as shown in Figure 1, check the box next to each field in the table for which you want a filter to appear. In this example, we choose only to filter the data by Service Line. After you select all the fields for which you want a Slicer, click OK to close the Insert Slicer dialog box. Upon doing so, Excel adds the filter to the worksheet.
Once added, you can drag-and-drop the filter to reposition it and customize the format of the the filter by manipulating the options on the Slicer Tools Options tab of the Ribbon. Most importantly though, you can simply click on the button presented on the Slicer to filter the table to your specifications. As shown in Figure 2, the table is filtered to just the Technology Services service line via the Slicer filter.
Tables are one of Excel’s most powerful features; however, when you add Slicer filters to your tables, you really extend their usefulness by making it easier for you and your team members to quickly filter tables without having to resort to the traditional drop-down filter environment. When working with tables, do not overlook the usefulness of Slicers!
We encourage you to watch the video below to see Slicers in action.