Tech Tips

  • Excel Tech Tips
  • 12.20.18

Creating a Clear Slicer Button in Excel

  • by 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

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

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

TT2dec2018c

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

 

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

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

VBA code courtesy of howtoexcelatexcel.com.


Back to Tech Tips