Slice and dice in Excel

Excel 2010 has Slicers

Excel 2010 introduced a new filter interface for Pivot Tables called Slicers. This feature improved the filtering process for Pivot Tables and made filtered selections more visible.

Slicers abilities have been expanded in Excel 2013 – but that’s the subject of a future blog post.

If you select multiple items in a filter in a PivotTable it is not obvious what the filters being applied are. See cell B2 in example below.

Multi Filter example

This page filter for State has two states chosen to display. Looking at the Pivot Table you can’t tell which states. You can see it in the filter selection (below) but not on the report.

Filter shown

Enter Slicers. A Slicer performs a filter on a Pivot Table. It is a graphic object that can be moved anywhere on the sheet and it also displays the applied filters.

(The file I used for the screen shots can be downloaded at the end of the post.)

To add a Slicer simply click a cell in the Pivot Table and click the Options Ribbon tab and click the Insert Slicer icon drop down and choose Insert Slicer.

Add Slicer

You then choose the field to link to the Slicer and click OK. You can choose multiple fields.

Choose Field

A Slicer is added to the sheet. You can move and re-size the Slicer. The Slicer below is performing the same State filter as before and it’s obvious what filters are in place.

Muliti Filter Slicer

You can hold down the Ctrl or Shift keys to select multiple items.

The symbol in the top right of the Slicer clears all the filters.

When you select the Slicer its Options Ribbon tab is displayed. This is where you can change the colours and sizes of the Slicers.

Filter Multiple Pivot Tables

The other advantage of Slicers is they can be linked to more than one Pivot Table. Normal Pivot Table filters won’t allow you to do that. One Slicer can filter multiple Pivot Table reports.

To link the Slicer to another Pivot Table right click the Slicer and choose PivotTable Connections.

Link Slicer to more pivot tables

Then select the other Pivot Table(s) to filter and Click OK.

Select Pivot Table

Below are two Pivot Table reports filtered by the same Slicer.

Slicer for two reports

Slicers Example

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.