Interacting with Slicers

In part 2 of this 3 part series introducing slicers, I’ll show you some of the ways you can interact with slicers. Part 1 can be found here.

Slicers reveal where the data is

Let’s say I want to slice on my customer data for yesterday, and I only want to see a list of customers with Partial College and Bachelors education levels. As I click on these two tiles to select them, they turn blue.

As it turns out, yesterday all my customers were either Partial College or Partial High School. I had no customers that held Bachelors or Graduate Degrees, so these tiles are grayed out.

My slicer would look like this:

For comparison, if I had created this with a report filter instead of a slicer, it would’ve looked like this:

Slicers play well with each other

Cross-Filtering

Slicers can cross-filter, which means they interact with each other to show you where the data is. Start with one slicer that shows the number of customers in different countries:

Add a marital status and children slicer. I’m interested in the single customers with one child, so I click on “Single” and “1”. The chart updates instantly.

You may have noticed that “Canada” in Country and “4” in Children become grayed out. Now you know there are no single customers with one child in Canada, and no single customers with 4 children anywhere. The grayed out tiles have also dropped to the bottom, out of your way. This is particularly useful for slicers with more items, so the data you care about is at the top.

Auto-Select

Related, but slightly different from cross-filtering is auto-selecting. When slicers are part of the same hierarchy, they auto-select each other. Examples of hierarchies are Year/ Month/Day and Country/State/City. Let’s drop the marital status and children slicers, and replace them with state and city.

Here, I’ve selected United States. Notice the states and cities in the US automatically become selected.

I can also pick from the state or city slicer. Here, I’ve clicked on Darmstadt, and the slicers auto-select Hessen and Germany.

Sharing your slicers

Recall the example in which I’m interested in the data for single customers with one child. I’ve already got a PivotChart with the number of customers – but now I want a separate PivotChart with profit data for the same customers. In Excel 2007, I would have had to create separate report filters for each. And if I decided I was instead interested in married customers with no children, I would’ve needed to manually update my selection in each report filter.

Slicers overcome this limitation – you can connect your slicers to multiple PivotTables, PivotCharts, and/or CUBE functions, and any selections you make automatically update everything the slicer is connected to.

Now let’s suppose these two PivotCharts were on separate worksheets in the same workbook, and you wanted the slicers to appear in each worksheet. By simply copying and pasting, you create a cloned slicer. When you change what you’ve selected in one slicer, all cloned copies of that slicer also update instantly.

Working with CUBE functions

So you’ve seen how slicers interact with PivotTables and PivotCharts. Let’s look at an example of how they work with CUBE functions.

Here I’ve added three slicers to my spreadsheet. When I write my CUBE function, connecting to slicers is easy. Slicers expose whatever members are selected as an Excel named range. Since named ranges appear in the formula auto complete dropdown, you can see what slicers you can connect to right in the auto complete dropdown.

Next time, I’ll show you how you can format slicers to make great looking reports.