Easy (and Even Fun!) Data Exploration: Introducing Excel 2010 Slicers

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

For the first version of slicers, the Excel team wanted to enable you to:

• See what you’re doing at all times


• Easily interact with your data


• Filter multiple PivotTables, PivotCharts, or CUBE functions together


• Put together dashingly handsome reports

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.