Filtering Multiple PivotTables That Share a Cache

Contributed by: Dawn Griffiths

Difficulty level: Beginner

Problem

You have multiple PivotTables based on the same table and want to filter them using a single set of slicers and timelines.

Solution

Suppose you have two PivotTables that share the same PivotTable cache (see Recipe 11.28). You want to insert one or more slicers and/or timelines and use them to filter both PivotTables.

Follow these steps to solve this problem (see Figure 1):

1. Select a cell in the first PivotTable and use Recipe 11.14 to insert the slicers and/or timelines.

2. Select a cell in the second PivotTable, then choose PivotTable Analyze ⇒ Filter ⇒ Filter Connections to open the Filter Connections dialog box.

3. The Filter Connections dialog box lists the slicers and timelines used by the first PivotTable because they share the same PivotTable cache. Place a check against any you also want to apply to the second PivotTable, then click OK.

Figure 1. The Filter Connections dialog box

You can also select a slicer or timeline and specify which PivotTables to apply them to by choosing Slicer ⇒ Slicer ⇒ Report Connections, or Timeline ⇒ Timeline ⇒ Report Connections.

Discussion

This recipe shows you how to filter multiple PivotTables using the same slicers and/or timelines as long as they share the same PivotTable cache; this is usually the case if you’ve ...