Why Slicers

Pages in this article

1. Why Slicers
2. Configuring Slicers
3. Slicers and VBA
4. Synchronising Slicers
5. Show Selected Items

If you've ever done filtering on Pivottables before you'll know why. But I'll spell it out here in case you haven't.

With any version of Excel, you can filter the content of your Pivottable report (or chart for that matter) by clicking the appropriate dropdown and checking/unchecking boxes:

Old-style Pivottable filtering

This method has a number of drawbacks listed below, in no particular order:

• Users who are not familiar with Pivottables are easily confused by the filter dropdowns.
• To check/uncheck an item you must click in the checkbox, clicking the item only selects the item, it does not change the check.
• You cannot drag across the items to select a number of them.
• You cannot modify the appearance of the filter, apart from changing the size of the entire box.
• There is no way of telling which filters are in effect, only that there is some filtering applied.
• If you have more than one Pivottable tied to the same data, then applying a filter to Pivottable 1 does not necessarily mean Pivottable 2 gets the same filter settings; it takes VBA to keep them in synch automatically.
• If your report has hierarchical data (countries and cities for example), then a filter on a higher level field in the hierarchy will not change the items visible in the lower fields. So Excel will happily show Toronto, even when you've unchecked Canada in the country filter. This makes filtering on large Pivottables very cumbersome.

Slicers put an end to a number of these problems:

Slicer Pivottable filtering

• They are very intuitive to use.
• You can click an item, drag across a number of them, use control or shift click, in other words they are easier to use.
• The appearance of a slicer is very configurable.
• You can tie one slicer to as many Pivottables as you like, keeping them in sync.
• You can have multiple "copies" of a slicer operating together on different worksheets.
• Slicers reveal hierarchical data in a sensible manner: non-applicable values are shaded differently so you'll know they do not apply in the current filter mode.

Next: Changing slicer settings