Excel Pivot Table Slicer Tutorials
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.
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 ***
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 and “Check” 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.
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:
Let me show you quickly how you can resolve this problem in just a few steps!
STEP 1: Go to File > Convert
STEP 2: This will convert your Excel file into a more updated version.
Click Yes to reload your workbook.
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!
January 31, 2021