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 dat in your Pivot Tables.
Slicers provide buttons that you can click to filter PivotTable 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. In this example below which is an extract of the number of data reccords at each sales stage in my prospecting database you can easily see that the data is filtered on the appointment data.
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 worksheet- like 4). Whether this is deemed a lot is probably a matter of personal opinion….. and how versed you are in manipulating the worksheet using the Pivot Table Slicers but the consus 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 worksheet.
Sub ClearMySlicers()
Dim Slcr As SlicerCache
For Each Slcr In ActiveWorkbook.SlicerCaches
Slcr.ClearManualFilter
Next
End Sub
- Open up your Excel worksheet and hit F11 to open the Visual Basic Editor. (if you need to enable the Developer Tab you can find out more here)
- In the left hand drop down box double click on the workbook 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 delveoper 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 everytime we want to reset the Pivot Slicers, but it is more simple to just provide the workbook users with a button to click.
Attaching the Macro Code To A Button
- Select the Developer Tab – Controls Group – Insert – Form Controls
- Select the Push Button Icon
- Click where you want to place the button on your worksheet
- 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.
If you want to download the practice file you can do so here. [ddownload id="8827"]
Create A Timestamp In Excel Using VBA
Delete Cell Content In Excel With Some Simple VBA
Delete values form cells but keep formulas
Transpose Excel data from rows to columns
[amazonjs asin=”1118490398″ locale=”US” tmpl=”Small” title=”Excel 2013 Power Programming with VBA”]
[amazonjs asin=”0470475358″ locale=”US” tmpl=”Small” title=”Excel 2010 Power Programming with VBA”]
[amazonjs asin=”1119077397″ locale=”US” tmpl=”Small” title=”Excel VBA Programming For Dummies”]