Pinterest Pixel
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.

Excel Table Slicers

Download excel workbookTable-Slicers.xlsx

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 ***

How to Lock the Workbook but Not the Slicer in Excel

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 Lock-the-workbook-Unlocked.xlsx
Download workbook – lockedLock-the-workbook-Locked.xlsx (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!

Slicer Connection Option Greyed Out For Excel Pivot Table - Easy Fix

Download excel workbookSlicer-Connection-Greyed-Out.xls

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

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!