ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tips & Tricks #487 – An Introduction to Slicers

Lego graph image

Author: Thomas Edmunds

Published: 09 Feb 2024

Exclusive content
Access to our exclusive resources is for specific groups of students, subscribers and members.

Hello and welcome back to Excel Tips and Tricks! This week, we have a Basic User level post exploring the basics of setting up, customising and using slicers in Excel to filter and sort through data.

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.

Revisiting inserting hyperlinks

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. 

Revisiting inserting hyperlinks

(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.

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

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.

Revisiting inserting hyperlinks

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).

Revisiting inserting hyperlinks

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. 

Revisiting inserting hyperlinks

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.

Revisiting inserting hyperlinks
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).
Revisiting inserting hyperlinks
Clicking and dragging (or pressing Shift) works for selecting multiple adjacent items.
Revisiting inserting hyperlinks
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.
Revisiting inserting hyperlinks

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.

Revisiting inserting hyperlinks
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.
Revisiting inserting hyperlinks
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.
Revisiting inserting hyperlinks
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.
Revisiting inserting hyperlinks

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.

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

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.

Revisiting inserting hyperlinks

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.

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250