Excel Tips & Tricks #487 – An Introduction to Slicers

Slicers are tools in Excel for filtering data in Tables, PivotTables and PivotCharts. They are a great way to add visual impact and interactivity to your workbook.

Slicers are easily recognisable as a list of clickable buttons that allow you to choose which items you want to show in your data.

In this article we will cover the process of setting up and using slicers. We will also look at some of the options available to customise them.

There is an optional Excel workbook that can be downloaded for anyone wanting to follow along with the examples in this article. 

Adding a slicer to a table

Here we have some sales data stored in an Excel table. 

(Note: if you are working with data that is not stored in a table then you will need to convert it to a table by pressing CTRL + T before inserting a slicer.)

Now, making sure that the currently active cell is anywhere in our table, we can insert a slicer by choosing “Slicer” from the Insert tab on the ribbon.

We will be prompted with a list of all the column headings in the table.

You can choose one or multiple checkboxes – each one selected will create a separate slicer. After making your selection choose “OK” and the slicers will appear in your worksheet. 

Here we have chosen slicers for Region and Product.

If you choose multiple slicers, they appear on top of one another – not very useful!

But as regular objects in Excel that float above the grid they can be moved about as desired by clicking and dragging in the normal way.

Each slicer shows one button for each unique item in the respective table column, sorted in alphabetical order (or numerical order, where the underlying data is numerical rather than text based).

Notice how by clicking “North” all the other items in the Region slicer have been unselected. This corresponds to the filter state in the Table – rows where the region is “East”, “South” or “West” are no longer appearing.

Note also how “Product A” is automatically shown as faded in the Product slicer even though we haven’t applied a filter to the Product column. This is because there are no instances where “Product A” was sold in the “North” region – as can be seen from the table. 

Slicer functionality

Selecting multiple items in a slicer works in the same way that you might select multiple cells in a range. Try holding down Ctrl and clicking on multiple buttons in a slicer. 

Using the slicer

Clicking any of these buttons will apply the relevant filter in the table. Here we have selected “North” from the “Region” slicer.

Tip! Clicking on the “Multi select” button in the top right corner allows you to select individual items without pressing Ctrl. This may be useful for those accessing the workbook on a touch screen device e.g., tablet (although noting that slicers, as at the time of writing, do not work on Excel for mobile).

Clicking and dragging (or pressing Shift) works for selecting multiple adjacent items.

And finally, clearing your selection, which has the effect of selecting everything and returning the slicer to its unfiltered state, can be done in a single click by choosing the “Clear Filter” button.

Visual display tips

Where the slicer contains a long list of items it can be helpful to show these items in multiple columns. The option to change the number of columns can be found in the Buttons group on the Slicer tab of the ribbon.

In this example spreading the buttons over three columns allow for easy access to all buttons and shows us the current filter state without having to scroll up or down within the slicer.

Where multiple slicers are in use, a quick way to increase visual impact is to select a slicer and choose a style from the Slicer Styles in the ribbon.

Here we have added slicers to all the columns in the sales data example and taken advantage of the built in styles to add a bit of colour.

What about PivotTables and PivotCharts?

No difference! Slicers work in exactly the same way with PivotTables and PivotCharts as they do with Tables.

Creating PivotTables and PivotCharts is outside the scope of this article, but you can learn more about PivotTables in Tip #190 and PivotCharts in Tip #367. 

Let’s assume we already have a PivotTable and PivotChart linked to our data as shown below.

We can insert a slicer by right clicking on any of the fields in the PivotTable Fields pane on the right-hand side.

Here we have inserted a slicer linked to the Product field.

Any selection we make within the slicer will be automatically updated in the PivotTable and associated PivotChart. 

In this case we have filtered for Products A and C, and excluded Product B.

Conclusion

Slicers are a great way to provide a visually inviting and user-friendly way to interact with your data. 

Next time you are working with data in a Table, or a PivotTable or PivotChart consider using slicers to increase visual impact and user engagement with the data.