All You Need to Know About

Excel Pivot Table Slicer Tutorials

Learn how to add slicer to Pivot Tables in Excel. These allow you to select Pivot Table items to filter with beautiful interactive buttons.

Here are the top things on what you can do with the Pivot Table Slicers in Excel:

Excel Table Slicers

Slicers were introduced with Pivot Tables in Excel 2010 and they allowed us to select items to filter with beautiful interactive buttons.

In Excel 2013, Slicers have been extended to be used with Excel Tables.  WOW!

To insert a Slicer in an Excel Table you have to follow these short steps:

STEP 1: Click inside the Excel Table

STEP 2: Select Table Tools > Design > Insert Slicer

STEP 3: Tick the Table Headers that you want to include in your Slicer and press OK

STEP 4: Click on the Slicer buttons and see how your Excel Table gets filtered without needing to select the filter drop down.

DOWNLOAD EXCEL WORKBOOK

Lock the Excel Pivot Table but not the Slicer

Sometimes when you are sharing an Excel Pivot Table with your colleagues you do not want the other user(s) to mess with your Pivot Table layout and format.

What you can do is lock the Pivot Table and only allow the user(s) to select the Slicers, making your report interactive and secure from Excel novices like your boss :)

Did you know you could also Lock the Workbook but Not the Slicer in Excel?

*** Watch our video and step by step guide below with free downloadable Excel workbook to practice ***

Here is how you can lock the Pivot Table but not the Slicers:

STEP 1: Click on a Slicer, hold the CTRL key and select the other Slicers

STEP 2: Right click on a Slicer and select Size & Properties

STEP 3: Under Properties, uncheck” the Locked box and press Close

STEP 4: Go to the ribbon menu and select Review > Protect Sheet

STEP 5: Uncheck” the Select Locked Cells andCheck” the Select Unlocked Cells & Use Pivot Table Reports

STEP 6: Enter a password (optional) and press OK

DOWNLOAD WORKBOOK – UNLOCKED

DOWNLOAD WORKBOOK – LOCKED (Password to unlock: myexcelonline)

Slicer Connection Option Greyed Out For Excel Pivot Table

Sometimes when you create a Pivot Table and want to insert a Slicer you are unable to do this as the Slicer button is greyed.

You try to click on the Slicer button but nothing happens.

What gives??

Slicer Connection Greyed Out

There are two things that can cause your Slicer connection to be greyed out!

ONE: Your file format is in an older/incompatible format (e.g. an .xls file extension)

TWO: You can see the text [Compatibility Mode] right beside the name of your excel file:

Slicer Connection Greyed Out

Let me show you quickly how you can resolve this problem in just a few steps!

DOWNLOAD EXCEL WORKBOOK

STEP 1: Go to File > Convert

Slicer Connection Greyed Out

Slicer Connection Greyed Out

STEP 2: This will convert your Excel file into a more updated version.

Click OK.

Slicer Connection Option Greyed Out For Excel Pivot Table

Click Yes to reload your workbook.

Slicer Connection Option Greyed Out For Excel Pivot Table

Voila! You can now insert your slicer!

NB: You can also Save As your current file as an .XLSX file format.  Then close this file and open it again and you will be able to use the Slicer button again!

Slicer Connection Option Greyed Out For Excel Pivot Table

Latest Tutorials

Want to get better at Excel?

Join Our Excel Community

Get our Free Weekly Excel lesson plans that cover the must know Excel features and tips to make you better at Excel!
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]