Filtering Tables in Excel 2013 with Slicers

Sometimes when you apply a filter to a table it isn’t obvious what filter has been applied. This is the case when you are filtering by multiple entries in a field.

Slicers were introduced in Excel 2010 to filter Pivot Tables. Filtering a Pivot Table has the same issue when filtering by multiple criteria in a single field. Click here to see a post on these Slicers.

Slicers’ abilities were expanded in Excel 2013 to include filtering formatted tables. (A formatted table is one created using the Format as Table icon on the Home Ribbon tab or the Table icon on the Insert Ribbon tab).

The advantage with using a Slicer is that it is visual. It shows the filter being applied. A normal filter is hidden once selected.

The disadvantage with Slicers is the filters are limited to selecting or deselecting entries in the list. There is not the flexibility provided by Excel‘s normal filtering options.

Adding a Slicer

In the Design Tab when a formatted table is selected there is an Insert Slicer icon.

Insert Slicer icon

A dialog displays allowing you to select fields to add Slicers.

Slicer selection dialog

 

Slicer added

To select multiple items hold the Ctrl or Shift keys whilst clicking the entries.

Slicer Filter applied

As you can see the Slicer shows the states being filtered.

To clear the filter click the icon on the top right of the Slicer.

Clear filters

Macro to save all open Excel files

Time saver

If you have a number of linked Excel files and you make a structural change (insert rows or columns) you need to save all the open files to ensure that the links are updated and retained. This can be tedious as there is no Save All option in Excel. Word has a Save All option, but not Excel.