[ad_1]
I have recently been working on a spreadsheet solution based very much on Pivot Tables and a good deal of Slicers to enable an easy of drilll down into the data. If you have been using any version of Excel from Microsoft Excel 2010 onwards, then you probably have already used slicers to navigate around the data in your Pivot Tables.
Slicers provide buttons that you can click to filter Pivot Table data, but in addition to the quick filtering, slicers also indicate the current filtering selection, which makes it easy to understand what exactly is shown in a filtered Pivot Table report.
So this drill down seemed to be all well and good, with my spreadsheet solution, but one issue that did come up was the number of clicks it took to ‘Start Over’ or reset all of the Slicers back to show all of the data. (There were quite a few slicers on the work sheet- like 4). Whether this is deemed a lot is probably a matter of personal opinion… and how versed you are in manipulating the work sheet using the Pivot Table Slicers but the feed back from this audience was that there were too many clicking options.
The challenge was that the slicers were needed to be there to drill down but took too long to reset.
So, I decided to use a teeny piece of code to enable one button to reset all of the Slicers with one click. So here is the small piece of code for my macro which I then attached to a button on the work sheet.
Sub ClearMySlicers()
Dim Slcr As SlicerCache
For Each Slcr In ActiveWorkbook.SlicerCaches
Slcr.ClearManualFilter
Next
End Sub
- Open up your Excel work sheet and hit F11 to open the Visual Basic Editor.
- In the left hand drop down box double click on the work book and paste the code in the window. (This will create a macro that runs when we select it. This macro will be called ClearMySlicers).
- x out of the developer window and we can check on the macro
- Developer Tab – Code Group – Macros to bring up the Macro Dialog box.
We could hit Run from here every time we want to reset the Pivot Slicers, but it is more simple to just provide the work book users with a button to click.
- Select the Developer Tab – Controls Group – Insert – Form Controls
- Select the Push Button Icon
- Click where you want to place the button on your work sheet
- The Assign Macro Dialog Box will appear
- Select the Macro we just created and hit OK
- Right click on the button to change the text in Edit Text if you need to- in my example I changed it to Start Over!
And that is it, all we need to do is test it. Works like a dream. Everyone is happy.
[ad_2]
