Filter Multiple Pivot Tables With Excel 2010 Slicers

Filter Multiple Pivot Tables With Excel 2010 Slicers

iconslicer In Excel 2007, and earlier versions, you can use Excel VBA code if you want to automatically filter multiple pivot tables at the same time. That task is much easier in Excel 2010, thanks to the new Slicer feature.

Slicer Intro

We took a look at slicers in October, in the Excel Slicers for Halloween Horror Films blog post and Slicer intro video.

To insert a slicer for a pivot table,

  • Select any cell in the pivot table.
  • On the Options tab of the Ribbon, click Insert Slicer.

slicersconnect00

  • Check the fields for which you want to add slicers, then click OK

slicersconnect01a

The slicers appear on the worksheet, and you can resize them and move them. Then, click on the pivot items, to filter the pivot table.

slicersconnect01

Connect Another Pivot Table

If you create multiple pivot tables from the same pivot cache, you can connect them to the same slicers, and filter all the pivot tables at the same time.

To create the Slicer connection in the second pivot table:

  • Select a cell in the second pivot table
  • On the Excel Ribbon’s Options tab, click Insert Slicer
  • Click Slicer Connections

slicersconnect03

In the Slicer Connections window, add a check mark to each slicer.

slicersconnect02

Both pivot tables are now connected to the Slicer. If you select an item in a slicer, both pivot tables will be filtered. For example, in the Product slicer below, Paper is selected, and both pivot tables show only the Paper sales.

slicersconnect04

Watch the Connected Slicers Video

To see the steps for connecting multiple pivot tables to a slicer, please watch the short Connect Slicers to Multiple Excel 2010 Pivot Tables video below.

________________