Bacon Bits:

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?

• Previous Entry: Excel 2010 Customizable Ribbon
• Next Entry: Merry Christmas Nerds