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
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.
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.