How to Use Excel Pivot Table Slicers

What is a slicer in Excel?

Excel slicers might have nothing to do with cake, but they’re a sweet way to visually segment selected portions of an Excel Table or Pivot Table. They can be used as alternatives to filters, but they are even better because they can be used to drill down into specific sections of a report and can even be linked to multiple tables. With just one click, a slicer can help you summarize several reports to display only the variables you want to see.

If you have Excel 2010 or later, then you’ll be able to use slicers on your data.

Let’s see what makes slicers so great by adding one to a pivot table that summarizes a six-year list of nominees and winners in two film categories.

The pivot table organizes the data nicely, but it is still a lot of information, which can make it difficult to find and extract the data we want. 

How to add a slicer to a pivot table

To insert a slicer, data must be stored in an Excel Table or a pivot table.

1. Click any cell within the table.
2. Go to Insert > Slicer.
3. Choose the category or categories that you want to be able to filter by.
4. Click OK.

As a best practice, slicers should be placed in a location where updates to the pivot table can be immediately seen when slicer filters are applied. This can be done by using the handlebars to move or resize the slicer(s).

Slicer components

First, we’ll do a walkthrough of what you can expect to see when working with slicers.

• Header: The slicer header indicates the pivot table field containing the values to be displayed or hidden.
• Multi-Select button (Excel 2016 and later): Click this button to select more than one item at a time. For earlier versions, hold down the Ctrl key while selecting multiple items within a slicer.
• Clear Filter button: Click to remove all filters from the report. When grayed out, this means no filters have been applied. 
• Scroll Bar: This indicates that there are more items available that will be visible by scrolling up or down.
• Handle Bars: These eight handles allow you to resize or drag and drop the slicer as you would do with any graphic object.

How to use a slicer in Excel

To use a slicer to filter the data in this table, we first need to remember that in their default state, the data in the table is displayed in an unfiltered format. To make slicers start working for us, we would click any one of the items in a slicer to see how it instantly changes the table display. 

For example, to see the nominees and winners in both categories for the year 2015, we would just click “2015” in the “Year” slicer.

This will deselect the other years and update the pivot table to display only films for that year.

To view only the 2015 winners in both categories, select “Winner” from the Status slicer, while “2015” is selected in the Year slicer. This will deselect the “Nominee” button.

Multi-Select

To select multiple items within a slicer, click the Multi-Select button, then choose the items you want to view.

Once the Multi-Select button is highlighted, it acts like the Ctrl button and will allow you to select any item in addition to those already selected. Clicking on a selected item will deselect it. To revert to single-selection, click the Multi-Selection button again to release it.

Delete a slicer

To delete a slicer:

1. Hover over it until a four-directional arrow appears. 
2. Click on the slicer and press the Delete key on your keyboard.

Slicer, gone!

Filter multiple pivot tables with one slicer

So far, you’re probably liking slicers quite a bit, but you’ll love them when you see the next example. You can use a single slicer to control the display of…wait for it….multiple pivot tables. 

Yes, it’s true. Here’s how to connect another pivot table to your slicer control:

1. Make sure each pivot table was created using the same source data. This means that if the source of the first pivot table was an Excel table called “My_Source”, the other pivot table(s) must use the same table name. Otherwise, it won’t work.
2. Set up the slicer for the first pivot table as usual.
3. Click on the slicer then go to “Report Connections” from the Slicer tab.
4. Select the reports you want to be linked to this slicer, then click OK.

Now whenever you click on the slicer items, all the pivot tables connected to that slicer will be filtered to reflect your selection.

In the example above, selecting the year 2017 shows the names of the nominees and winners for that year in the first pivot table, while simultaneously showing the number of nominees and winners for that same year in the second pivot table.

This feature also makes slicers an excellent tool for increasing the interactivity of dashboard elements.

Lock the Pivot Table but not the slicer

If you want your audience to be able to play around with the display without breaking your pivot table, you can lock the pivot table but not the slicer by doing the following:

1. Right-click the slicer
2. Select Size and Properties… from the contextual menu.
3. On the Format Slicer pane, choose Properties then uncheck the “Locked” checkbox.
4. On the ribbon, go to Review > Protect Sheet
5. In the Protect Sheet dialog box, uncheck “Select locked cells”
6.  Check “Use PivotTable and PivotChart”.
7. Enter a password (optional) and click OK. (If you forget the password you will not be able to unlock the workbook.)

Users will now be able to click buttons within the slicer to gain insights into specific segments of the data, but will not be able to change the layout of the pivot table itself. Learn more about other options to protect specific ranges in a worksheet.

Slicer styles 

You can change a slicer’s color theme by selecting a style from the Slicer Styles gallery.

Create your own slicer style

You can further customize Excel slicer formatting styles by doing the following:

1. Click the down arrow and then select the New Slicer Style… command. 

2. From the New Slicer Style window, you can choose a name for your new style.

3. Below, select a slicer element then click the Format button to control how that element will appear when your new slicer style is applied. You can format as many elements as you like. A Preview will be shown in the window before the slicer style is created. 

4. Click OK and the new style will appear at the top of the gallery.

Create a new style from an existing one

Alternatively, you can create a new slicer style by duplicating an existing one then modifying the duplicate.

1.  Right-click on the existing style you will use as the basis for your new style
2. Click “Duplicate…”
3. In the Modify Slice Style window, enter a name for the new style and format the elements you want to change.
4. Click OK. 

Slicer settings

To control a slicer’s behavior and display settings: 

1. Click on the slicer then go to the Slicer menu on the ribbon. 
2. Click the Slicer Settings command. This will open the Slicer Settings dialog box.

From here you can change the displayed header name, the sort order of the slicer buttons, and how to represent elements that have no data.

Add columns to a slicer

If your slicer has too many items and scrolling is inconvenient, multiple columns might be the solution:

1. Click the slicer and go to the Slicer tab.
2. Look for Columns within the Buttons command group.
3. Adjust to the desired number of columns.
4. Press Enter.

Adjust slicer size or location

Adjust slicer size

As mentioned above, you can adjust the size of the slicer simply by dragging the handlebars on the outer edges. Alternatively: 

• Select the slicer and click the Slicer tab. 
• Use the Height and Width fields within the “Size” command group to set the slicer to a specific size.

This is especially useful when you want slicers to be identical in size.

Adjust button sizes

To adjust the size of the item buttons within a slicer:

• Select the slicer and click the Slicer tab.
• Use the Height and Width fields within the “Buttons” command group to make the buttons wider or narrower.

Align slicers

👉Moving a slicer around is easy enough - just hover over it until a four-directional arrow appears, then click and drag to the new location. Or use the Copy and Paste commands.

👉To move multiple slicers all at once, hold down the Ctrl key to select them all. Hover to get the four-directional arrow, then drag, and drop.

👉If you want several slicers to be lined up with each other, select them all by using the Ctrl key then go to Slicer > Align. Then choose the alignment option from the drop-down menu.

Troubleshooting pivot table slicers

Here are a few tips if you encounter problems working with slicers in a pivot table.

Data still filtered after slicer is deleted

If you deleted one or more slicers but the data in your pivot table is still limited to the selections you made on the slicer, simply look for the filter funnel(s) in the table header.

Click the icon and check the “Select All” checkbox from the drop-down menu. This will un-filter all the data in that category.

Slicer command is disabled

If you have a version of Excel earlier than Excel 2010, you will not be able to add a slicer to your table. But what if you have a version that is compatible with slicers and you find that the Insert Slicer command is grayed out when you select a table or pivot table? In that case it is possible that the file was originally created in an older version of Excel, or was saved as an XLS file. 

Here’s how to fix that:

1. Go to the File menu and choose Save As.
2. Choose .XLSX as the file type and save the workbook.
3. Close the workbook and open the file in the updated format.
4. Click on the pivot table then Insert > Slicer will be enabled.

Unable to connect a slicer to another table 

If you want to connect multiple pivot tables to a slicer but the Report Connections (Status) window does not display the report you want to add, this means the pivot tables were not created using the same source. 

Here is how to check:

1. Click inside the pivot table that the slicer is already connected to.
2. Go to PivotTable Analyze > Change Data Source
3. Take note of the Table name or cell range used as the pivot table source
4. Click Cancel
5. Click inside the pivot table you want to connect to the slicer
6. Go to PivotTable Analyze > Change Data Source
7. Take note of the Table name or cell range used as the pivot table source. The Table name or cell range must be identical to the previous one to allow you to connect the slicer to that pivot table. 

Go ahead, be awesome!

Now there’s nothing stopping you from making your pivot tables even more versatile with the use of Excel slicers. Learn even more ways to filter and extract data from pivot tables with our expert-led Pivot Tables course!

Ready to become a certified Excel ninja?

Start learning for free with GoSkills courses

Start free trial