Excel 2010 Slicers

A lull in the action this week gives me time to talk about the new features in Excel 2010 that strike me as real improvements.

Today, I want to share my thoughts on Slicers.

 

With Excel 2010, Microsoft introduces a new feature called Slicers.

Slicers allow you to filter your pivot table; similar to way Filter fields filter a pivot table. The difference is that Slicers offer a user friendly interface that allows you to easily see the current filter state.

 

Creating Slicers

In Excel 2010, we find a new command under the PivotTable Options tab called Insert Slicer. This will activate the Insert Slicers dialog box where we can select the dimensions we will want to filter.

In this example, the Region and Market Slicers will be created.

 

Once the slicers are created, we can simply click the Slicer values to filter the pivot table.

As you can see, clicking on Midwest in the Region Slicer not only filters the pivot table, but the Market slicer responds by highlighting the markets that belong to the Midwest region.

 

 

Slicer Advantages

The fact that we can visually see the current filter state, gives Slicers a unique advantage over the Filter field.

For instance, in this screenshot, the Ctrl key was held down while Baltimore, California, Charlotte and Chicago were selected.

This not only highlights the selected markets in the Market Slicer, but it also highlights their associated regions in the Region Slicer.

If we were to use the standard Filter field, the pivot table would show the text “Multiple Items”. There would be no apparent indication which markets were selected.

 

 

Another major advantage Slicers have is that they can be tied to more than one pivot table. That is to say any filter you apply to your Slicer can be applied to multiple pivot tables.

To connect a Slicer to more than one pivot table, we can simply right-click the Slicer and activate the PivotTable connections dialog box.

Then, we simply place a check next to any pivot table which we want to filter using the current Slicer.

 

At this point, any filter you apply to your slicer will be applied to all the connected pivot tables. Again, slicers have a unique advantage over Filter fields in that they can control the filter state of multiple pivot tables. Filter fields can only control the pivot table in which they live.

 

Slicer Disadvantages

For all the advantages that Slicers provide, I personally don’t think I will use them that often (if at all). Mainly because of two reasons:

1: No Backwards Compatibility with Excel 2007

PivotTable Compatibility is still a major issue with Excel 2010. Many of the new PivotTable functions such as Slicers will simply not work in Excel 2007.

Unlike the other new functions (such as Percent of Subtotals and Repeat Item Labels), Slicers are specifically designed to be used by a reporting audience. If that audience is still using Excel 2007, they get no love from Slicers.

 

2: Slicers are bulky

There is no way to say it nicely. Slicers are huge and they take up lots of real estate.

Take a look at this screenshot pulled from the Microsoft website. Most of the page is taken up by Slicers.

Now, I’m no Stephen Few, but I’m pretty sure this breaks all kinds of Dashboarding best practices.

 

 

When I’m bored, I sometimes play a game I call “Find the Pivot Table”.

 

I wish Microsoft gave us the option to auto-collapse the Slicers. That is, make it so that they expand only when you hover over them. I think this would make them usable.

 

There you have it. After taking a close look at Slicers, I’ve decided I don’t like them.

They do offer some significant advantages over the standard PivotTable Filters, but I find them ugly and unwieldy. Plus, they don’t play with Excel 2007.

What do you think?

13 thoughts on “Excel 2010 Slicers

  1. AlexJ

    Very cool function, very good discussion. Slicers would do a great job for my reporting apps, but we’re still 2003 here. Instead I use VBA to set page fields across multiple pivots.

    Does VBA have access to the Slicers object?

  2. ross

    Hi Mike,
    [Happy xmas]
    Good post. I’ve not looked at slices yet, but it seems to me, that they are basically a new control type, and that there not perfect.
    Hats off to MS though they are trying hard in this area, and are making some ground up. I think getting this stuff backwards compatible might be impossible. Are the days of backwards compatibility behind us?

  3. datapig Post author

    Ross: I think you may be right when you say that backwards compatibility is a thing of the past.

    Starting with Office 2007, Microsoft has definitely gotten over any qualms about backwards compatibility.

    I understand Microsoft’s dilemma though. If they box themselves into a strict backwards compatibility model, they wouldn’t be able to expand to new and innovative territory fast enough.

  4. ROBERT R

    The Slicers worked great and connected to the all the pivot tables when the data was part of the spread sheet.
    It seems that when the pivot table was created (linked to an access data base) the slicer does not connect to the other pivot tables in the spread sheet..

  5. David Lary

    I have to agree that slicers take up too much screen space and need to be dockable and hideable much like in VSTO development. We thought that perhaps we could put them in CTPs
    (Custom Task Panes) but find no way of doing that and I don’t see any benefit to it since you still have to take up screen realestate.

  6. Jeff Weir

    There’s been some improvement to slicers if you’re using the PowerPivot add-in. Quoting from Bill Jelen’s “Powerpivot for the Data Analyst”:
    * slicers are intelligently resized
    * powerpivot makes some intelligent guessing about the layout of slicers.

  7. Daniel Carrier

    Can Slicers and/or PowerPivot access entire Table reports or PivotTable reports instead of just fields within reports?
    i.e.: click for Income Statement “Actual vs Budget”; then click for, “Monthly with Budget” Income Statement, and so on.

Leave a Reply

Your email address will not be published. Required fields are marked *