Creating a Clear Slicer Button in Excel

​Josh Rosales, Research Analyst, El Camino College

Scenario: You are developing an Excel dashboard based off a Pivot table or tables and you have multiple slicers to allow different combinations of data disaggregation, as in the image below. Everyone “oohs” and “ahs” until they realize what a hassle it is to uncheck each slicer in order to review the next combination of interest.

TT2dec2018a.png

This is where the slicer refresh button comes in. You can create a button that will unselect all slicers, returning your interactive graphs/charts/tables to their default form.

First, you need to have the Developer tab enabled on the ribbon. If you already see it on the ribbon, you can skip this part, but the Developer is not automatically enabled. Right-click the “Home” tab (really, any tab will do) and select “Customize the Ribbon…” from the drop-down menu. This will open a dialogue box. Ensure you are looking under the Main Tabs section under “Customize the Ribbon” on the right and select the checkbox for “Developer” below it. Select OK.

TT2dec2018b.PNG

The Developer should now be available on the main ribbon. In the Developer tab, click “Insert” under the “Controls” Section.

TT2dec2018c.PNG
The first option under the Form Controls menu should be “Button (Form Control)”. Select that option. Draw the box on your worksheet that will become your button. Once the button is created, you will get the Assign Macro dialogue box. The first time you do this on your machine, you have to insert the VBA script. I have already created mine, so the ClearMySlicers macro is already available, thus, I can automatically select this option for any new workbooks I create in the future.

TT2dec2018d.png

The first time you are creating this, select New and paste the following script in place of the existing text that starts with:

Sub Button#_Click()

End Sub

TT2dec2018f.PNG

Close the VBA screen. Your macro is automatically saved. Right-click the button you created and then “Assign Macro”. Select the ClearMySlicers option and your filter is ready to go. 

As a last step, you should right-click your button one more time and choose "Edit Text" to name your Button. You should also add a description of how to use the slicers and “CLEAR FILTERS” button for readers, as shown in the example below.

TT2dec2018e.png

VBA code courtesy of howtoexcelatexcel.com.

 
 
 

 Comments

 
To add a comment, Sign In
Total Comments: 1
 
Shawn posted on 12/20/2018 2:52 PM
This is an extremely useful tip, thank you! Your tip comes at a time when I am working on a project using multiple slicers and need an easy way to return to start without having to go through six slicer boxes getting there.