Excel VBA Tip: How to Reset All PivotTable Slicers with One Button Click

I’ve recently been working on a spreadsheet solution based heavily on PivotTables and a lot of Slicers to allow easy drilling into the data. If you’ve been using any version of Excel from Microsoft Excel 2010 onwards, you’ve probably already used slicers to navigate data in your PivotTables.

Slicers provide buttons you can click to filter PivotTable data, but in addition to quick filtering, slicers also indicate the current filtering selection, making it easy to understand exactly what is displayed in a PivotTable report. filtered pivot table.

So this breakdown seemed to be fine, with my spreadsheet solution, but one issue that arose was the number of clicks required to ‘Start Over’ or reset all slicers to display all data. (There were quite a few cutters on the worksheet, like 4). Whether this is considered much is probably a matter of personal opinion… and how well versed you are in manipulating the worksheet using PivotTable Slicers, but the response from this audience was that there were too many options to click.

The challenge was that the cutters needed to be there to break down, but they took too long to reset.

So, I decided to use a little code to enable a button to reset all slicers with one click. So here is the little code snippet for my macro which I then attached to a button in the worksheet.

SubClearMySlicers()

Dim Slcr as SlicerCache

For each Slcr in ActiveWorkbook.SlicerCaches

Slcr.ClearManualFilter

next

finish sub

• Open your Excel spreadsheet and press F11 to open the Visual Basic Editor.
• In the dropdown box on the left, double-click the workbook and paste the code into the window. (This will create a macro that will run when we select it. This macro will be called ClearMySlicers.)
• x outside the developer window and we can check the macro
• Developer – Code Group – Macros tab to open the Macro dialog box.

We could click Run from here every time we want to reset Pivot Slicers, but it’s simpler to provide workbook users with a button to click.

• Select Developer tab – Control Group – Insert – Form Controls
• select push button icon
• Click where you want to place the button on your worksheet
• The Assign Macro dialog will appear
• Select the macro we just created and press OK
• Right-click the button to change the text in Edit Text if necessary; in my example, I changed it to Start Over!

And that’s it, all we have to do is try it. It works like a dream. Everybody is happy.