You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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.
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 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.
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.
It makes more sense to have an options that says "dont show items with no data"
So if I select a particular Country only states belonging to that country should show up.
"It makes more sense to have an options that says "dont show items with no data"
So if I select a particular Country only states belonging to that country should show up."
This then precludes the ability to select state or city independently of country.
If you want city or state independent of country, why not remove the filter (slicer) for country?
If you want city or state independent of country, why not remove the filter (slicer) for country?"
So that you can set country independently of city or state? :) In the end, I think that it really depends on how you want to see the data filtered. In many scenarios you obviously won't need all three slicers.
posting for karen, she's having some trouble leaving comments atm:
To address some of the comments here:
The feature you're suggesting would definitely be useful. While slicers don't have that setting, they do have a related one, which is turned on by default: a checkbox for "Show items with no data last." So states not belonging to that country would be greyed out and would show up last.
Slicers do have a similar option turned on by default: "Visually indicate items with no data." So if you want to filter state/province and country independently of each other, you can.
Where's the MS site?
Need to download the viewer...
You got hacked?
Very nice feature, makes life for end users much easier and it is easier for BI consultants to train end users in these slicers compared to pivot table filters.
It would be handy if one could dock the slicers in a certain position, maybe minimize them at the bottom or right hand side. Right now, when manipulating the slicers /pivot table, they hide data from view, and it is a bit cumbersome to move them around.
This is not a complaint, just some suggestions for improvement...
Thanks for your comment. More user-friendly manipulating of the size and position of the slicers is something we'll investigate for future versions. For now, slicers do resize intelligently in Gemini: