Slicers were introduced in Excel 2010 for PivotTables. What are slicers you ask? If you don’t know, well, you’re in for a treat. Here is some background information to bring you up to speed.

Introducing Excel 2010 Slicers

Use slicers to filter PivotTable data

PivotTable Slicers by Jan Karel Pieterse

So you should know that slicers are an effective way to visually filter your data on a PivotTable, and cue you in to what is now visible and what isn’t. These tools have grown in popularity and are an outstanding improvement to the product.

If you noticed, most of those links are dated around late 2010. Why the lull, you may ask? Well, it wasn’t until Excel 2013 that tables received this functionality! As nice as it would have been to have them in the 2010 release, it didn’t quite make it. But now we have the power! If you’re visual like me, you’re going to love these nifty little creatures of data filtering. Let’s walk through where to find them and how to use them.

Where to find them

You can find the slicers in two locations. Either on the TABLE TOOLS | DESIGN ribbon tab, which only shows when you have a table part selected, or on the INSERT tab.

How to use them

Before we start, the example I’m using is just some dummy data, which looks like this:

Nothing very elegant, but it serves as a good example. I want to filter ‘Col1’ of the data (pictured above), but with a slicer.

1. Select the INSERT tab (my preference)
2. Select ‘Insert Slicer’
3. Check the columns you want to add a slicer to (each check will create its own slicer)
4. Click OK

That’s it! For this blog post I’ve added a single slicer for ‘Col1’.

Which creates a slicer that looks like the following, and you should see a contextual ribbon tab appear that is labeled SLICER TOOLS | OPTIONS:

Now you have a table slicer in place! Click on each of the values to filter the table for that value. Some rules the slicers will follow:

• You can add as many slicers as you want
• You can add multiple slicers for the same column (but they are bound together, so one affects all the others – so pretty much a waste of time)
• To select multiple items you can Ctrl + Click, or Shift + Click for contiguous items
• Only unique values will be shown

Interacting with a slicer

If you select a slicer item you will filter the table (which filters the entire sheet row), as seen below.

It’s just that simple.

Clearing a slicer filter

This is just as easy as applying a slicer filter. Click the filter icon with the little red ‘x’ in the top-right corner, as seen below:

Customizing a slicer

You may be thinking that slicers are cool, but how do I really make them look and work well. Not to fret, there’s plenty of customizable options. If you used slicers for PivotTable’s in Excel 2010, things haven’t changed much. Here’s the basic run-down:

Change the slicer itself, styles, arrangement, button specifics & size of the slicer object itself

The Slicer Styles gallery has 8 pre-defined light styles and 6 pre-defined dark styles:

These styles are all theme-based, as the colors are theme tints, so if you choose a different theme these colors will update to reflect those changes. If you’re feeling up to it you can create your own slicer style. Just click the ‘New Slicer Style…’ button at the bottom of the slicer styles gallery. This is what you’ll see:

If you’ve made custom table or PivotTable styles, this should look very familiar, as it’s the same basic interface. You have various elements you can format and the ability to set it as default for any new slicers you create. If you like one of the built-in style sand want to set it as default, just right-click it and choose ‘Set As Default’:

Slicer settings

Right-clicking a slicer will get you some options, but the ones to pay attention to are the bottom two, ‘Size and Properties…’ and ‘Slicer Settings…’. By clicking on ‘Size and Properties…’ you will be prompted with the slicer taskpane:

In here are the same options you have on the ribbon for sizing, as well as the more important Properties section. By default they don’t move or size with cells – which is a good thing. If you had a slicer to the left or right of your table, and the setting was on move and size with cells, then your slicer could go away due to the rows being filtered!

As a side note, if you’re creating files for somebody else, think about putting in text into the ALT TEXT portion. This is really important for accessibility options for the visually impaired. Trust me, they use Excel too.

The other option when you right-click a slicer is ‘Slicer Settings’, which you can also find on the ribbon. Selecting that will bring up a dialog box with some options:

Here you can change the Caption of the slicer, which is handy to give a pretty name to it, without having to change your column headers. Most of this is pretty standard, but the ones you’ll want to pay attention to are in the bottom-right corner. Items with no data to show will persist through on your slicers, so if you don’t want to show them you’ll have to check that box. Doing this will gray out the next two options. However, if you don’t check that box, you can choose to visually indicate items with no data.

These checkboxes cascade because those underneath are dependent on those above. The one that gets people confused the most is the last checkbox, ‘Show items with no data last’. With this option your unique items will be re-arranged to show those with data first, going from left-to-right, top-to-bottom. If you don’t want them re-arranged, uncheck this box.

Summary

Slicers are a powerful tool and can bring you a rich UI to your Excel files. Not only can you visually see what’s going on with your data, they’re very easy to interact with, and you’ll wow those who haven’t seen them yet.