top of page

With great control comes great responsibility - Slicers Part 2/2


In this final part of this two-parter, I'm going to show you how to use one slicer to control more than one pivot table.

Remember, slicers are only available in Excel 2010 or above.

Example

The slicer in the first-parter is pretty basic - it just shows how many offences were committed in each Council Area. I can add additional slicers to drill down my data even further.

I’m going to add another slicer on ‘Offence Category’ so I can see how many offences were committed in a particular Council Area in a particular category and which Offence Category was committed in which Council Area.

Warning - in order for slicers to work / be controlled with multiple PivotTables, they must share the same PivotTable cache. The easiest way to do that is to copy pivot tables and adjust the fields from there.

To enable my slicer to control multiple pivot tables:

STEP 1

I'm going to click on the slicer that I just created (the tab SLICER TOOLS – OPTIONS will pop up) > under “Slicer” click “Report Connections”. Alternatively, I can right-click my slicer and select “Report Connections”.

STEP 2

Select the PivotTable(s) that I want my slicer to control. I’ll know that my PivotTables(s) don’t share the same PivotTable cache if they’re not listed in “Report Connections”.

STEP 3

My end result will look like:

In the first image, I can see that in the City of Darebin, all offences were committed except in the "Burglary/Break and enter" and "Drug use and possession" offence categories.

In the second image, I can see "Drug dealing and trafficking" occurred in all Council Areas except Macedon Ranges Shire and Melbourne City.

Have a go at creating slicers here: Crime Statistics

Notes: Categorical data and images of this tutorial are based on the following:

留言

bottom of page