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.
Thanks to Karen Cheng for putting together this series on Slicers.
New to Excel 2010 are slicers, which are visual controls that allow you to quickly and easily filter your data in an interactive way. They float above the grid, like charts and shapes.
Here’s an example of the same slicer in three different states. You can select one, all, or some countries. A slicer acts like a report filter, so you can hook it up to a PivotTable, PivotChart, or CUBE function to create an interactive report.
Shows data for all countries
Shows data for Canada
Shows data for Australia, Canada, and Germany
For the first version of slicers, the Excel team wanted to enable you to:
Let’s dive into each one of these goals.
In this example, I want to analyze the profit generated by customers with 1 or more children who also hold a Bachelor’s degree or higher. In Excel 2007, I could add “Number of Children” and “Education” to the report filter in my PivotTable and select the corresponding items. However, the report filter displays as “multiple items,” which is not very helpful.
In Excel 2010, we’ve added slicers to your toolbox so you can see what you’re doing at all times. It’s now completely clear what data the report is showing you.
Filtering your data in Excel 2007 was pretty cumbersome. First, you click on the drop down arrow or filter icon, expand any items as necessary, check/uncheck items until you get what you want, and then click OK.
Report Filters: Four Steps
Slicers: One Step
In contrast, selecting a tile in a slicer is easy – it takes one click. To select multiple tiles, use combinations of ctrl+click, shift+click, or click+drag. This behavior mimics keyboard interaction you may already be familiar with for cells, list boxes, or lists icons in Windows. Slicers look like controls rather than cells, so they’re just asking to be played with. Using slicers to create your reports will invite interaction, enabling you and your collaborators to spend less time sifting and more time analyzing.
One of the limitations of report filters were that they had a 1:1 relationship with the PivotTable they were filtering. If you wanted to apply a filter to multiple PivotTables, you were out of luck – you’d have to recreate that filter for each PivotTable. Now, you can connect slicers to PivotTables, PivotCharts, and/or CUBE functions to your heart’s content. Anything you do in the slicer will conveniently apply to everything it’s connected to. More on this in my next blog post.
Slicers can be customized in many ways, and you can now create beautiful dashboards that wouldn’t have been possible before. In a future blog post, I’ll go over in more depth some of the different ways you can dress up your slicers. For now, here’s a sneak peek of the kinds of reports made possible by slicers:
Oh and one more thing - slicers work with Excel Services 2010 and Excel Web App too, so you can work and share on the web.
So this concludes the introduction to slicers. Over the next week or two, I’ll dive more in-depth about slicers. I’ll show you some of the nifty ways you can interact with and format slicers, as well as how to make your reports look great.
It looks very impressive. When is it due to release?
Can't wait to tinker with that!
I like Slicers and they can be a great boon however I've noticed they are bit finicky.
First off I had a large spreadsheet in .Xls (
How much formatting do the slicers allow for?
In the example, they seem to be taking up way more space than they deserve.
Also, do the slicers allow for a checkbox on/off display format, where clicking the slicer rectangle area turns a checkbox within the rectangle "on" or "off"?
In the report's pie chart made up of Mountain Bikes (blue), Road Bikes (red), and Touring Bikes (green),
wouldn't it make more sense to have the color of the slicer match the color you see in the pie chart?
Similarly, in the line chart the colors are blue for Europe and red for North America, yet the slicer color for both of them is green?
Wouldn't it make more sense to have a bullet-point color box inside the slicer rectangle instead of giving the slicer an inordinate amount of attention with the vivid background colors?
And is there no built-in ability to match up slicer color with chart color?
Posting for karen, she is having trouble leaving comments:
To address some of the comments here:
Office 2010 is coming in the first half of 2010.
First issue - You might be able to get your slicers to work- after you save as .xlsx, try refreshing your data (Data > Refresh All). Also, make sure you're not including new items in the manual filter. (Row labels > right click the field > field settings > Include new items in manual filter should be unchecked).
Second issue - Could you clarify what you mean by default not to new series vs collectively together? When I create a PivotChart with every year as a new series - let's say 2001 had sales of 1, 2002 had sales of 2, and 2003 had sales of 3 - If I select 2002 and 2003 in slicers, the total would be "5" as expected.
You can change slicer formatting to whatever colors you wish, and you select them independently of what chart colors you're using (they don't match up automatically). They can be formatted to take up as much or as little space as you want (see upcoming blog post for examples of this). You can also hook up a slicer's formatting settings to a checkbox with a macro.
Very very impressive..... it will be good if u attach a sample sheet......