Microsoft Excel is a spreadsheet program that is part of the Office 365 Suite and has been the traditional reporting tool for businesses. Although I know that many companies are slowly shifting their reports to Power BI because it offers powerful analytics and reporting features, I still see Excel as a great tool to present data in a compelling way.

In this tutorial, I will explain how to create some interaction using slicers to filter data in Excel.

What Is a Slicer?

A slicer is an object in Excel that allows you to filter data in the form of an interactive buttons.

Slicers provide buttons that you can click to filter tables, or PivotTables. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is currently displayed

Source: Microsoft Office Support - Use slicers to filter data

The slicer shows all the different values for a selected column and each is displayed as a button inside the slicer. You can click on the buttons to active and deactivate a filter from your data set. It floats above the grid of the spreadsheet and is always visible for use.

Slicers were introduced for Pivot Tables in Excel 2010 for Windows, for Tables in Excel 2013 for Windows. It was introduced to Excel for Mac only it the 2016 version.

Adding Slicer

As mentioned in the Microsoft’s description above, a slicer can be used with a Table and a Pivot Table.

Adding Slicer to a Table

To add a slicer to a Table, you need first a table. To create a table from a common spreadsheet data, use the following steps:

1. Select the first cell (A1)
2. Press the keys “CTRL” + “SHIFT” + “Arrow Right” to extend the selection to the last cell right
3. Press the keys “CTRL” + “SHIFT” + “Arrow Down” to extend the selection to the last cell down
4. Click on “Data” icon
5. Click on “From Table/Range” icon
6. Click on “OK” to confirm the range
7. Click at the “Close button” at the Power Query Editor (I won’t use this here)
8. Click on “Discard” (Don’t need to save)

And the table is ready.

With the table ready, adding a slicer is very simple. Just click on Table Design [1] and later at the button Insert Slicer [2].

Select the columns you would like to use as a filter and press OK. Resize and move the slicers to an appropriate location on the spreadsheet and you are ready to use the slicer.

Adding Slicer to a Pivot Table

Below is a quick step-by-step guide to create a simple Pivot Table, so I can show you how to add a slicer:

1. Select all cells with data (Same as previous explanation)
2. Click on “Insert”
3. Click on “PivotTable” icon
4. Click on “OK” to confirm the range
5. Drag the field “Category” to “Rows”
6. Drag the field “Title” to “Values” (By default, the field has “Count” calculation type, which is the expected choice for this example)

And the PivotTable is ready.

In this example, I am using data extracted from the Sakila Sample Database of MySQL. The simple PivotTable shows the number of movies for each category.

To add a slicer for a Pivot Table is also very simple:

1. Click on “PivotTable Analyze”
2. Click on “Insert Slicer”
3. Select a field
4. Press “OK”

We now see the number of movies per category and we can filter according to the rating.

In such a simple pivot table, the slicer could be seen as irrelevant, since we can use the filter (see screenshot below). However, when selecting multiple values, the filter will display “Multiple Items”, but we can see the filtered items using the slicer. Filtering using the slicer is also just a button click. A bigger difference between filter and slicer is that the slicer can be connected to multiple pivot tables and pivot charts, while filters are linked to one pivot table.

For me, slicers are great when using with pivot charts. The screenshots sequence below shows a small part of an Excel automated daily report I created years ago for Project Mustang, which demonstrates the use of slicers with pivot charts.

Disclaimer: The data that appears in the screenshots above has been manipulated for presentation purposes and confidentiality reasons.

Using Slicer

Using a slicer is simple and you can select the items the same way you select cells in a spreadsheet. First, let us have a look at the objects of a slicer:

Note: I manipulated Sakila’s original data by changing all Horror movies with the G and PG rating to PG-13 in order to display the slicer above.

Select a Single Item

To select one item in a slicer, click on the item. When you do this, you are applying a filter and you will only see data for this item.

Select Multiple Non-Adjacent Items

You press and hold the Ctrl key, then you click on the items to select or unselect them.

Select Multiple Adjacent Items

To select multiple adjacent items, you have 2 options:

1. Click on the first item, then drag the cursor to the last item and release the mouse button
2. Click on the first item, press and hold the Shift key, click on the last item and release the key

Enable Slicer Multi-Select Mode

Excel provides an easier way to select multiple items by using the “Multi-Select” mode. Click on the “Multi-Select” button in the upper right hand corner of the slicer (next to the Clear button) to enable multi-select.

Clear Selected Item

You can unselect all items (reset the filter) from a slicer by clicking at the “Clear” button located at the top right corner of the slicer.

Customizing Slicer

The look of a slicer can be customized. To change the default blue color, do the following:

1. With the slicer selected, click on the “Slicer” tab
2. Open the “Slicer Styles” group
3. Click on the style you want to use

You can further customize it by creating new slicer styles. Click on “New Slicer Style…” [4] and you will see many options to choose from.

After adding a pivot chart, making some quick adjustments and creating a new slicer style, we have:

I hope this tutorial will help you use slicers in Excel. Thanks for reading and come back in future to see new posts!