Using Slicers to Make Your Excel Report More Dynamic

One of the many new features introduced in Excel 2010 were Slicers. Slicers sounds fancy, but just think of them as Filter Buttons. They sit on top of Pivot Tables to make it easier to filter your data, and is a great way of presenting your data to someone else, who perhaps wouldn’t notice / know how to use the Pivot Table Filters.

They’re very powerful to use; they allow you to have more than one slicer to a given pivot table, but you also can link multiple pivot tables to the same set of slicers, meaning users do not need to set the same filter on each – but they’re also easy to set up.

For this walk-through, I’ll assume you’re relatively familiar with Pivot Tables – but you needn’t be an expert!

I’m using some simple data showing sales by product by day.

With your data, Select INSERT from the Ribbon, PivotTable to create a pivot table

Follow the steps to create a pivot table of your liking. I’m just going to show total sales by line

Now, with your Pivot Table selected, pick the INSERT ribbon tab and select Slicer

You should be asked to which field(s) you would like to use. I’m going to go with Category

Once you click ‘OK’, you should see the slicer appear…

The slicer will list all the available categories. Clicking on a category filters your pivot table…

Use your [CTRL] key to select multiple items, and you can format it to match your pivot table…

Right-Clicking your Slicer give some further options. ‘Slicer Settings‘ lets you set display options, such as whether to show items that have no corresponding data.

Right Clicking the Slicer and selecting ‘Size & Properties‘à‘Position & Layout‘ allows you to make adjustments to the size of the buttons, and, helpfully, select how many columns you want to show – which allows you to make some useful layouts…

(Yes, I different data for this one)

As you can see, you can add more than one slicer to a Pivot Table.

I mentioned earlier that you can link one slicer to more than one Pivot Table. Note: This only works with pivot tables from the same data source!

To do this, create a new Pivot Table in the same way as before. Once you’ve created it, however, select the (new) Pivot Table, then select the Analyze tab from the ribbon and pick ‘Filter Connections’

Edit: In Office 2010, this option is available by selecting the Pivot Table, Options–>Insert slicer–>Slicer Connections.

You will then be presented with available slicers for you to link to your new pivot table…

Select the tick box and click ‘OK’. Now, when you change the slicer, both pivot tables will update.

And there you have it. A great and flexible way to present your data. Slicers also work on Pivot Tables, and in 2013 they can also be used on Excel Tables – making them extra useful – I’ll cover this off in more detail another time though.

Let me know how you get on with them.

1 comment