Pivottables are one of the most powerful data analysis features Excel has to offer. From version to version, Microsoft have added functionality to this feature. Pivottable Slicers were introduced with Excel 2010 and as of Excel 2013 you can also insert a slicer to filter a table. In this article I'll discuss how you can put slicers to work.
If you've ever done filtering on (Pivot) tables before you'll know why this can be a challenge. But I'll spell it out here in case you haven't.
With any version of Excel, you can filter the content of your table or Pivottable report (or pivot chart for that matter) by clicking the appropriate dropdown and checking/unchecking boxes:
Old-style (Pivot) table filtering
This method has a number of drawbacks listed below, in no particular order:
Slicers put an end to a number of these problems:
Slicer filtering
There is a host of settings you can apply to your slicers. The Ribbon shows most of them:
Ribbon (cut in two halves so it fits on this page) showing the slicer options tab.
The Slicer Tools contextual ribbon tab houses a number of groups to change your slicer's appearance and behavior. Let me discuss them here.
This group houses three important controls. You can change the name of a slicer here (Slicer Caption), change some settings controlling which items are listed in a slicer and how they are sorted and filtered (Slicer Settings). Also, you can set up which Pivottables connect to your slicer (I explain that below).
A quick way to format your slicers is by applying one of the built-in slicer styles. Like any style in Excel 2010, these adhere to the theme you've selected. This makes it easy to keep your workbook design tidy and consistent.
Like cell styles you can create your own slicer styles too, by right-clicking one of them and choosing "Duplicate", followed by right-clicking the newly duplicated style and choosing "Modify".
Just like you can easily change the colors of your slicers by applying a different style to the slicer it is simple to modify the button layout and appearance, just adjust the numbers in that group of the ribbon.
These three controls enable you to change the button layout and size
And last but not least a small group which lets you edit the button sizes.
Unfortunately, not everything you need is on the ribbon. The missing options are quickly accessible through the slicer's right-click menu however. Select the "Size and Properties" option to get there. Here you can control whether or not the slicer must be printed and should move/size with cells just like any other shape on a sheet. And you can turn off resizing and moving to prevent your users from messing with your layout.
If you've scurried through all slicer dialogs like I have, you'll have noticed there is no way to change the font size of a slicer.
Or is there? Of course there is, but is has been cunningly hidden!
The basic idea is that you must edit the slicer's style. But since you can only edit custom styles, the first step is to add a custom style. Pick one you like best and right-click it, then select Duplicate...:
Right-click a slicer style and select "Duplicate"
The second step is editing the new custom style. Right-click that style and select "Modify...":
Right-click a slicer style and select "Modify".
In the dialog that pops up, select "Whole Slicer" end then click the Format button:
The Modify slicer Quick style dialog.
As you can see in the screenshot below, now you can edit the slicer's Font.
Time to select a nicer font for your slicer!.
After you OK this dialog, probably nothing appears to happen to your slicer. Don't worry, all that is needed now is applying your new custom style to the slicer and you're done!
A very useful option of slicers is that you can tie them to more than one Pivottable and/or -chart.
The way to handle this is by selecting the slicer in question and then clicking the Pivottable Connections button in the Slicer Settings group on the Slicer Tools contextual ribbon tab:
the Pivottable Connections button in the Slicer Settings group on the Slicer Tools contextual ribbon tab.
This brings up the following tiny dialog:
the Pivottable Connections dialog.
As you can see, Excel has listed a couple of Pivottables in your file to tie the slicer to.
Luckily, Excel is smart enough to ensure that only Pivottables using the same cache as the selected slicer is tied to are in the list.
If you have more than one slicer on a particular field (e.g. a slicer on "City" on each sheet with a Pivottable), then you must take care when selecting which tables the slicer relates to. As soon as you check more than one check box, the slicers working on the checked Pivottables will be permanently tied together. The only way to make the slicers work independently again is by using the Undo button.
This chapter has also been published on The Microsoft Office Blog.
For each slicer you add to your workbook, Excel adds a SlicerCache object too, which controls which Pivottable(s) your slicer controls.
So suppose we have a workbook with two Pivot caches. Pivotchache1 has two pivot tables: Pivottable1 and Pivottable2. As soon as you add a slicer to a Pivottable (even if the slicer ties to the same field of the Pivottable) you get a SlicerCache object tied to the pivotCache to which the pivot table belongs:
Hierarchy of the slicer and its family
Note that the above picture is not entirely correct. In the object model, PivotTables are in fact tied to a slicerCache, not to a slicer. But on the user interface side of things it is the slicer the user actually sees, which is why I put that in-between.
Also note that this is a diagram for pivot table slicers. A table slicer has a much simpler diagram where a table has one slicer cache with attached to that all slicers pertaining to that table. No other tables are involved.
The code below enumerates all slicer caches and their associated pivot tables in your workbook:
As soon as you check more than one Pivottable on the Pivottable Connections dialog of a Slicer, the slicers in question will share a single SlicerCache object. The other SlicerCache object will be removed from the collection. This explains why you cannot go back by unchecking all but one Pivottable in that dialog: all joined slicers will now be changed by changing the checked Pivottable(s) on any of them. In turn, each checked Pivottable becomes part of the Pivottables collection of the remaining SlicerCache object.
If you decide to select Slicer1 and change its pivot connections by checking both Pivottable1 and Pivottable2, one slicer cache is deleted (the one belonging to the Pivottable you checked to add to the current slicer). So the hierarchy changes to:
Changed hierarchy of slicers
So both Slicer1 and Slicer2 control Pivottables 1 and 2. The slicers 1 and 2 are in sync too because it is in fact the SlicerCache that is changed by the slicer. So the hierarchy in the picture above isn't entirely true.
It is easy enough to change the button appearance using a bit of VBA:
Note that the numbers do not coincide with what is shown on the ribbon. Apparently the unit of measure differs between VBA and the ribbon.
Changing some aspects of your slicer using VBA isn't hard to do. In fact, the macro recorder makes finding out how this works relatively simple. After changing some settings and doing a bit of tidying up I got:
Oftentimes a reporting workbook contains more than one pivot table. As I showed you already, synchronising pivot tables that use the same pivot cache is very easy if you tie them together with one or more slicers pointing to both pivot tables. You use the "Pivottable Connections" button on the ribbon for that.
However, it frequently happens that pivot tables do not share a pivot cache, but still need to be in sync, for instance if your slicer is filtering on a reporting period such as Year, Quarter or Month. In the code below I assume all pivot caches share the fieldnames to filter on. I also assume the tied-together slicer caches share the same values, in other words, all years appearing in Slicer_Year also appear in Slicer_Year1 and vice versa. If your situation differs (values in slicer cache 1 might be missing in slicer cache 2 or the other way around), you will need to take special precautions.
The first action needed is to make sure each slicer is connected to all relevant pivot tables belonging to its pivot cache. Take care NOT to add a pivot table to a slicer for which the field in question is not used in that pivot table, because a slicer can operate on a field that isn't included in the pivot table itself. It will filter your pivot table and the pivot itself gives no clue that it was filtered (other than the slicer).
Suppose you have three reports: Monthly, Quartely and Yearly. You might have three slicers for those pivot tables: Year, Month and Quarter. But for the quarterly and yearly pivot table, it makes no sense to tie them to the Month slicer. In fact, it would turn them all into a monthly report. In a situation like this, only the Year slicer is a sensible one to connect to all three pivot tables. If you would want to connect the Quarter slicer to the Month slicer, you would need some VBA which, after selecting a month, sets the associated Quarter in the Quarter slicer. The code below isn't that smart, it just syncs Year, Quarter and Month slicers, assuming you did all of the slicers pivot table connections correctly.
There is no event tied to slicer actions directly. However, clicking a slicer triggers the Workbook_SheetPivotTableUpdate event in the ThisWorkbook module (there is a similar event in the sheet modules, but I want one generic event which responds to any pivot table update). The empty event stub looks like this:
Sh is an object variable pointing to the worksheet on which a pivot table just updated. Target is an object variable pointing to the pivot table that just updated.
The first thing the code needs to do is find out which slicers are connected to the pivot table that just changed and set a pointer to the ones we want to use to sync the other pivot tables to.
First, we declare some variables:
The first three are going to hold the Year, Quarter and Month slicers tied to the changed pivot table. Note that no more than two of these three are in fact tied to the pivot table: either Year and Month, Year and Quarter or just Year.
So now step through all SlicerCaches and then through all their associated pivot tables to find out which slicers are tied to the changed pivot table:
As you can see, we only pick up the slicers Month, Quarter and Year because those are the ones we want to sync.
Now that we have the slicer(s) that have been clicked, we want to sync them with slicers with similar names. By Default, Excel names SlicerCaches like this: Slicer_FieldName. If another slicer with the same fieldname is added (which adds a SlicerCache too), it is called Slicer_FieldName1, the next one is called Slicer_FieldName2 and etcetera. So what the code below does is loop through all slicer caches again, looking at their names to figure out which ones belong together.
The code has a special trick to avoid problems. Suppose you have a slicer which has its first item selected and the remaining items de-selected. Suppose that the sister slicer is clicked and we click the second item. When the code runs through all slicer items, it will start with the first one, which will be de-selected. This causes the slicer to select all items, because it is not allowed to de-select all items of a slicer. Subsequently the code runs through the remaining items and sets them as needed. The end result is that both item 1 and item 2 are selected, rather than just item 2. The trick around this is setting the last slicer item to "Selected" before running through the loop:
So if you put all of this together, this is what you end up with:
Even though it is often sufficient to see the slicer on the workbook it can be useful to be able to get a list of the filtered sliceritems in a worksheet cell. If your pivot table is built using a Power Pivot model, then you can access this information by using the CUBERANKEDMEMBER worksheet function. If however the slicer was built off of a "normal" pivot table, then you need a bit of VBA code in a User Defined Function (UDF).
In order to retrieve the selected slicer items we need slicer's internal name, both for the CUBERANKEDMEMBER function and for the UDF. This name can be shown in the user interface by right-clicking the slicer in question and selecting "Slicer settings". The name you are looking for is marked in red in the screenshot below and indicated by "Name to use in formulas":
The CUBERANKEDMEMBER function returns an array of selected items and hence needs to be into as many (vertical) cells as you expect will be selected in the slicer. See the screenshot below.
The formula is:
=CUBERANKEDMEMBER("ThisWorkbookDatamodel",Slicer_ShipCountry,SEQUENCE(CUBESETCOUNT(Slicer_ShipCountry)))
Note, that this is a dynamic array formula that only works in Excel 365. For older versions you should use a formula like the one below and copy that formula down far enough:
=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",Slicer_Name,ROW()-ROW($G$2)),"")
The code sample below must be placed in a normal module:
The function takes the slicer name as its argument and returns a text string (comma delimited) containing the selected items. In a worksheet cell the formula would look like this:
=GetSelectedSlicerItems("Slicer_TeamID2")
Well, I hope I got you started with getting your head around how slicers work and how to address them using VBA. I find Slicers a real gem in Excel. A great addition to the product!
Download sample Excel workbooks (29 Aug 2019, downloaded 5.146 times)
Microsoft Excel - Easy (and Even Fun!) Data Exploration: Introducing Excel 2010 Slicers
Microsoft Excel - Interacting with Slicers
Wall Street Oasis - Slicer in Excel: Tool Guide - Images and Video Instructions in Excel
All comments about this page:
Comment by: General Ledger (12-1-2011 19:22:29) deeplink to this comment
WOW!! How did you discover all this great stuff? Thanks for revealing these secrets. Please keep this information coming. GL
Comment by: Vishwanath Nayak (17-1-2011 23:22:11) deeplink to this comment
Dear, I recently purchased a new Dell optiplex 380 with a MS excel non commercial use,, but i dont see this Slicers ribbon/options, tools in my excel while playing with pivots....How should i debug this ? Regards, Vn
Comment by: Jan Karel Pieterse (18-1-2011 01:04:14) deeplink to this comment
Hi Vishwanath, What Office version are you using? SLicers were introduced with Excel 2010 (=Office 2010) and are unavailable in Excel 2007 and older. If you do have 2010, make sure you select a cell inside a pivot table so that the Pivot tools tab is visible on the ribbon. The slicer button is on that tab.
Comment by: Rick Freitas (9-2-2011 16:28:04) deeplink to this comment
Hi Jan, I noticed in the slicer settings box there is a "Use Custom Lists when sorting" check box. Do you know how you call a custom list to sort items in slicer box? Assuming probably VBA code to do that but haven't been able to find any examples. Thanks for your help with this. Regards, Rick
Comment by: Jan Karel Pieterse (10-2-2011 05:30:12) deeplink to this comment
Hi Rick, I assume this works exactly the same as "normal" custom lists are used when Excel does sorting. I'd look up custom lists in Excel help.
Comment by: Pradeep (10-2-2011 15:51:34) deeplink to this comment
Hi, I am using excel 2010 but can not find slicer option on ribbon. Evenif i select a cell in piot table it doesnot show slicer option in pivot table tab. Do i have to download it as and addin. Pradeep
Comment by: Jan Karel Pieterse (11-2-2011 03:59:08) deeplink to this comment
Hi Pradeep, It is located in the Sort & FIlter group, on the Options tab of the Pivottable Tools contextual tab of the ribbon. Note that this option is disabled if you are in a pivot table in a file that was saved in Excel 97/2003 format. It has to be a "normal" Excel 2007/2010 format workbook.
Comment by: Guy Brown (3-3-2011 09:52:15) deeplink to this comment
Slicers are the best part of Excel 2010 - especially the ease with which one can connect multiple pivot tables with a single slicer. Unfortunately there appears to be not backward compatibility with Excel 2007 at all (I'm surprised Microsoft have not come up with a "compatibility pack") - even in xlsm format. Is there a way of maintaining the pivot connections when opening a "slicered" 2010 workbook in 2007? (though in 2007 I know the old style "check box" filters would have to be used)
Comment by: Jan Karel Pieterse (4-3-2011 02:03:47) deeplink to this comment
Hi Guy, Not sure what is supposed to happen. The information I had so far was that 2010 objects are ignored by Excel 2007, but still included in a save. So I was expecting them to carry over unscathed. Are you saying they don't? If so, it may be useful to send me the workbook (the version before 2007 and after 2007 has touched it) so I can issue a bug report.
Comment by: Jason (29-4-2011 07:42:54) deeplink to this comment
I think the question may be slightly misrepresenting the issue. If you have already created a slicer, the Slicers ribbon/options, tools is a contextual tab/ribbon option that shows when you select the slicer itself. It will show up as the right most ribbon option when the slicer is selected.
Comment by: jason (29-4-2011 07:43:50) deeplink to this comment
I'm interested to know what "hovered" means in the slicer element section of of the above image. Thanks
Comment by: Jan Karel Pieterse (1-5-2011 21:25:50) deeplink to this comment
Hi Jason, Hovered means the format the slicer item gets when the mouse hovers over it.
Comment by: Jason Parrish (2-5-2011 05:48:16) deeplink to this comment
Thanks Jan.
Comment by: Sebastien (18-5-2011 05:50:45) deeplink to this comment
Hi, I'm trying to get the selected items from a slicer using VBA. I can't find how to use:
Comment by: Jan Karel Pieterse (18-5-2011 06:17:43) deeplink to this comment
Hi Sebastien, Have a look at this little macro, maybe it gets you going:
Comment by: Sebastien (19-5-2011 01:02:40) deeplink to this comment
I tried what you explained but I get "Run-time error '1004': Application-defined or object-defined error" because of SlicerItems. Do you know how to fix that?
Comment by: Jan Karel Pieterse (19-5-2011 02:44:56) deeplink to this comment
Which line is highlighted if you press debug? If it is the msgbox line, hover your mouse over each part of that statement, which part shows the error message in the tooltip?
Comment by: Sebastien (19-5-2011 03:28:41) deeplink to this comment
The error comes on the line: For Each oSi In ActiveWorkbook.SlicerCaches("Slicer_City").SlicerItems I tried to do Dim oSc As SlicerCache Set oSc = ActiveWorkbook.SlicerCaches("Slicer_City") For Each oSi In oSc.SlicerItems ... And the error comes on the For Each line. That's why I think the problem comes from SlicerItems.
Comment by: Jan Karel Pieterse (19-5-2011 06:37:48) deeplink to this comment
Hi Sebastien, Odd, as it works flawlessly for me. Does the slicer actually have any items visible?
Comment by: Sebastien (19-5-2011 06:48:17) deeplink to this comment
Yes, I tried with different slicers with some selected items or not. I don't really understand this error. Could it be a security issue or something like that?
Comment by: Jan Karel Pieterse (19-5-2011 07:40:28) deeplink to this comment
Hi Sebastien, Send me the file in question and I'll have a quick look. See email address near bottom of this page.
Comment by: Jan Karel Pieterse (22-5-2011 21:52:40) deeplink to this comment
Hi Sebastien, Found the problem. If your Pivot table is getting data from an external source, you must use the SlicerCacheLevels collection to find out what items are in the slicer:
Comment by: Andrew Fedor (2-6-2011 19:48:12) deeplink to this comment
I have 2 pivot charts in the same dashboard that are from 2 different datasets. In other words the tables that the pivot tables were bulit from are different. Is there a way to a single slicer to control both pivot charts? One chart is sales and the other is backlog and I want to look at both by salesman. So when I am looking salesman#1, I want to see sales for salesman#1 and backlog for the same salesman.
Comment by: Jan Karel Pieterse (5-6-2011 23:55:44) deeplink to this comment
Hi Andrew, As far as I know, Each pivot cache has its own set of slicers, so the short answer is no. But you could use some VBA code to synch two slicers. For example, this code -placed in the ThisWorkbook module- will synchronize two slicer caches. The code only responds to a change in a slicer named Slicer_City, located on Sheet2 and will update Slicer_City2 located anywhere:
Comment by: Andrew Fedor (6-6-2011 13:23:04) deeplink to this comment
Jan: Thank you for your assistance. I replaced your sheet and slicer names with mine from my workbook, but when I execute the code I get an error on line 6 "SlicerCaches("Slicer_City2....." The error is -- Method 'Selected' of object 'SlicerItem' failed. I checked the spelling of my sheet and slicer names and they are correct. Any ideas.
Comment by: Jan Karel Pieterse (7-6-2011 00:15:29) deeplink to this comment
Hi Andrew, If you email me your workbook I'll try to find a bit of time to look at it. Please add which slicers you want to work together (where they are located in the workbook)
Comment by: Ian Tibot (6-7-2011 05:35:03) deeplink to this comment
Hi Jan, I have the same issue as Andrew, my data is coming from an external source but from two different queries that have common elements. If you have found a way to effectively make a second slicer a slave to a 'master' slicer that would solve my problem. I've tried to use the code that you proposed for Andrew but when i go to run the macro it opens the run macro box and i have nothing to run. Cheers Ian
Comment by: Jan Karel Pieterse (6-7-2011 06:49:44) deeplink to this comment
Hi Ian, Try if code like this does any better. Note that this code should fire off automatically once you change a slicer. It is up to you to adapt the code to your slicer names and such!
Comment by: Jan Karel Pieterse (6-7-2011 06:50:33) deeplink to this comment
Hi Ian, NB: The code goes into the ThisWorkbook module!
Comment by: Shadi Al. Kurdi (27-7-2011 23:11:36) deeplink to this comment
Hi Jan, is there any possibility to do Custom sorting in Slicer items by VBA code? The only options available are A-Z or Z-A BR Shadi
Comment by: Jan Karel Pieterse (22-8-2011 03:01:54) deeplink to this comment
Hi Shadi, I have not tested this, but you might try adding a custom sort list that is in the order you want and then do the sort? Sort lists can be accessed through File, Options, Advanced tab, General category, Edit custom lists option.
Comment by: Trisha (27-8-2011 09:23:46) deeplink to this comment
My slicer function is missing also. My spreadsheet is saved in .xlsx version, but still missing. Any suggestions?
Comment by: Jan Karel Pieterse (29-8-2011 01:32:27) deeplink to this comment
Hi Trisha, If you add a new pivot table to the xlsx file, does that new Pivot table allow inserting of a slicer?
Comment by: RealParadox (29-10-2011 08:13:06) deeplink to this comment
I have problem with osi.selected = false Sub GetSlicerValues() Dim oSi As SlicerItem Dim oSl As SlicerCacheLevel For Each oSl In ThisWorkbook.SlicerCaches("Slicer_Year").SlicerCacheLevels For Each oSi In oSl.SlicerItems ''MsgBox "Slicer name: " & oSl.Name & ", Slicer value: " & oSi.Value & ", Selected: " & oSi.Selected & ", Available: " & oSi.HasData If oSi.Value = "2011" Then MsgBox "Yes" oSi.Selected = True Else MsgBox "No" End If '' MsgBox "Slicer name: " & oSl.Name & ", Slicer value: " & oSi.Value & ", Selected: " & oSi.Selected & ", Available: " & oSi.HasData Next Next End Sub maybe someone can help me?
Comment by: Jan Karel Pieterse (31-10-2011 01:07:57) deeplink to this comment
Hi RealParadox, What exactly is the problem?
Comment by: Vivek Gokhale (3-11-2011 18:16:56) deeplink to this comment
Hi!!! This article was great. Is it possible to wrap text data in the slicer button??? Would really help. Thanks
Comment by: Jan Karel Pieterse (4-11-2011 05:07:14) deeplink to this comment
Hi Vivek, I'm afraid not!
Comment by: Melroy Beeks (6-11-2011 15:21:11) deeplink to this comment
In my Micro soft does not have a SLICER button
Comment by: Jan Karel Pieterse (8-11-2011 00:53:15) deeplink to this comment
Hi Melroy, You need to have Excel 2010 to be able to use Slicers in Pivot tables.
Comment by: s hartman (18-1-2012 13:56:22) deeplink to this comment
How can I use one slicer when I have multiple pivotcaches (and multiple pivot tables)?
Comment by: Jan Karel Pieterse (18-1-2012 23:38:18) deeplink to this comment
Hi Seana, I'm afraid you cannot tie one (set of) slicer(s) to more than one Pivot cache. However, you can use a bit of VBA code to mimick that behaviour. Find some examples in the comments of the next page: https://jkp-ads.com/Articles/slicers03.asp?AllComments=True
Comment by: Dipti (20-1-2012 09:21:40) deeplink to this comment
Can i put slicer on the same page as the Pivot table?. I am trying to make it look like how we select from the drop down and get the related information in pivot table..
Comment by: Jan Karel Pieterse (22-1-2012 22:24:18) deeplink to this comment
Hi Dipti, Of course you can!
Comment by: Jason Bisulco (25-1-2012 06:13:50) deeplink to this comment
Can you group the data in a slicer? (e.g. in number incremeents say 1 - 20 or group monday - wednesday if a date.
Comment by: Jan Karel Pieterse (25-1-2012 07:38:59) deeplink to this comment
Hi Jason, No, but you can group the appropriate field of the pivot table and add a slicer for the grouped field.
Comment by: Andre (27-1-2012 10:45:01) deeplink to this comment
Hi Jan Karel, Is it possible to add value filters, i.e. values greater than 1,000,000 to the slicers? If so, what about multiple value filters? Cheers, Andre
Comment by: Jan Karel Pieterse (27-1-2012 11:11:42) deeplink to this comment
Hi Andre, I don't think ou can do that, no.
Comment by: Kajsa-Stina Ohlström (3-2-2012 07:40:48) deeplink to this comment
When i should connect my slicer to more then one of my pivot tables, there comes a message that it "the slicer can not currenty be created or connected to the pivot table". What's wrong ?
Comment by: David (4-2-2012 09:08:40) deeplink to this comment
Thank you so much for this resource! I have been searching everywhere for a way to link my slicers that do not share a cache with no success until now! I modified the code provided to work with three slicers that have different caches. Unfortunately I have to run the "For Each" section twice, otherwise about 20% of the time it selects all of the slicer fields, or nearly all of them. Any idea why?
Comment by: Kajsa-Stina (5-2-2012 23:46:01) deeplink to this comment
Hi When I try to add more than one pivot table connections to my slicer, I got a message that "An error occured and the slicer can not currently be created or connected to pivot tables". What's wrong ?
Comment by: Jan Karel Pieterse (6-2-2012 04:57:24) deeplink to this comment
Hi Kajsa-Stina, You can only add pivot tables form the same pivot table cache to one slicer, perhaps the pivot tables you are trying to combine are independently tied to their own source?
Comment by: Kajsa-Stina (13-2-2012 07:41:02) deeplink to this comment
Thank you for the answer. All pivot tables i try to connect to my slicer are using an area that I've named "rawdata" as datasource. This area is just in another sheet in the same workbook. When checking name manager, I can see that "rawdata" is right defined, however, my slicers are also listed in the name manager, but they have no reference... /Kajsa-Stina
Comment by: Jan Karel Pieterse (13-2-2012 08:12:58) deeplink to this comment
Slicers do not have a reference in Name Manager, that is "by design". How many Pivot caches does your workbook have? Alt+F11 to the VBA editor control+g to the immediate window type: ?ActiveWorkbook.PivotCaches.Count press enter with your cursor on that line.
Comment by: Kajsa-Stina (14-2-2012 00:48:22) deeplink to this comment
I've just 1 Pivot Cache (getting that after doing as you told)
Comment by: Jan Karel Pieterse (14-2-2012 03:00:29) deeplink to this comment
Hi Kajsa-Stina, In that case I truly have no idea why it does not work. Perhaps you should try to recreate the pivot tables in a blank workbook just to check if it does work?
Comment by: Kajsa-Stina (14-2-2012 04:45:44) deeplink to this comment
I've solved my problem by "remaking" all pivot-tables by copy/paste for all tables that should be connected to the same slicer. Now it works.
Comment by: Jan Karel Pieterse (14-2-2012 07:03:37) deeplink to this comment
Hi Kajsa-Stina, Excellent, glad you were able to solve the problem!
Comment by: Aaron (20-2-2012 14:01:12) deeplink to this comment
Once you've created a custom slicer template, is it possible to save it and access it on another workbook? I've created my own slicer template (color, font, shading, etc) that I want to use in different workbooks, but the "created" templates are only available within that one workbook. Cheers, Aaron
Comment by: Jan Karel Pieterse (20-2-2012 22:54:55) deeplink to this comment
Hi Aaron, A quick way is to open the workbook with the custom slicer style applied to a slicer (not sure of that is necesary) and copy the slicer and paste it into the other workbook. The slicer style is copied with it.
Comment by: Aaron (22-2-2012 15:36:32) deeplink to this comment
Cheers! That is quite simple and works perfectly!
Comment by: Midimal (28-2-2012 04:02:22) deeplink to this comment
Hi! Does anyone know how to determine using VBA if slicer filter is on or not? (similar what is possible with autofilter) thanks
Comment by: Jan Karel Pieterse (5-3-2012 05:12:11) deeplink to this comment
Hi Midimal, YOu could use this function to detect whether or not a slicers is in filter mode:
Comment by: Irvin Jaffe (7-3-2012 12:32:47) deeplink to this comment
RE: Excel 2010. I have a slicer with 6 choices. I want to show only 4 of the 6 (2 of the 6. And, of the 4 that I want visible, I want to change the text. How do I do this?
Comment by: Jan Karel Pieterse (8-3-2012 02:39:30) deeplink to this comment
Hi Irvin, I think you can only achieve that by removing data from the source so that those items are not on the slicer. Also, you cannot change what is shown on the slicer, other than by changing the source data.
Comment by: bill (29-3-2012 15:19:18) deeplink to this comment
Can someone tell me why I receive an 'application defined or object defined error' when I try to set a slicer selected mode to 'True' or 'False'? (example in below code: oSi.selected = True) Dim oSi As SlicerItem Dim oSl As SlicerCacheLevel Dim selected As Integer selected = 0 Dim slicerDateVal As Date Dim slicerDateText As String Dim i As SlicerCaches Dim k As Slicer Application.EnableEvents = False 'ActiveWorkbook.SlicerCaches("Slicer_Attendance_Date").ClearManualFilter For Each oSl In ActiveWorkbook.SlicerCaches("Slicer_Attendance_Date").SlicerCacheLevels For Each oSi In oSl.SlicerItems slicerDateVal = oSi.Caption slicerDateText = Format(slicerDateVal, "yyyy-mm-dd") If slicerDateVal >= startDate And slicerDateVal <= endDate Then 'oSi(slicerDateVal).selected = True If oSi.selected = False Then oSi.selected = True End If
Comment by: Jan Karel Pieterse (30-3-2012 01:54:14) deeplink to this comment
Hi Bill, Not sure why, but perhaps the item in question is greyed out on the slicer because of other filters being in place?
Comment by: Mark F. (3-4-2012 09:26:35) deeplink to this comment
I cannot find the spot to change the format of numbers in the slicer. Currently I am getting very generic looking numbers all left justified. Any solutions to this?
Comment by: Jan Karel Pieterse (3-4-2012 21:54:29) deeplink to this comment
Hi Mark, The number format of a slicer cannot be adjusted I'm afraid.
Comment by: Kathryn (3-5-2012 10:08:53) deeplink to this comment
How do I change the date format in a slicer button? My table data has the date as 6/1/2011, but I've formatted it to display Jun-11. However, the slicer button still displays 6/1/2011.
Comment by: Jan Karel Pieterse (3-5-2012 11:18:35) deeplink to this comment
Hi Kathryn, I just tried this and it looks like the slicer adheres to the date format as set in the source data table.
Comment by: Rob (16-5-2012 06:20:02) deeplink to this comment
I have slicers on one tab and pivot tables on another. When I copy these tabs to a new workbook, my slicers do not copy (or macro buttons for that matter). They used to copy fine when I went through this process, but I must have changed something along the way. Is there a way to reset this function ? Thanks, Rob
Comment by: Jan Karel Pieterse (16-5-2012 07:53:58) deeplink to this comment
Hi Rob, To be honest, I have no idea! Sounds like your file may be experiencing problems? Are you copying all sheets that belong to each other in one go?
Comment by: Rob (17-5-2012 04:03:16) deeplink to this comment
Hi Jan, I have tried the copy process using all the tabs and just single tabs. I have also tried it using the tab drag process from one open workbook to another and still lose my slicers and buttons. May have create a different work around....
Comment by: Dan (17-5-2012 04:46:15) deeplink to this comment
Hi Jan, I am trying to make one slicer change when another similar slicer changes. This is like other people above because I have information from more than one data source. I have ammended your code above for my workbook, the sheet is called "WWNI dashboard" and my slicers are called "ww_network_manager 3" and "ww_network_manager 4" So I have this code in the "ThisWorkbook"
Comment by: Jan Karel Pieterse (18-5-2012 07:21:55) deeplink to this comment
Hi Dan, What happens if you put a breakpoint in the code (first possible line) and then change the slicer that is supposed to trigger the event?
Comment by: Dan (18-5-2012 12:01:08) deeplink to this comment
If I put a breakpoint on "If mbNoEvents Then Exit Sub" line and click the slicer it goes to my vba, I can then step through line by line and it runs fine. I have made a few modifications below to make this happen as my sheetname was the name of the sheet where the slicers are, however it does not change the slicer selections. My pivots are made with powerpivot, I'm not sure if that makes a difference. I can send a copy of the spreadsheet if you like.
Comment by: Jan Karel Pieterse (20-5-2012 23:18:34) deeplink to this comment
Hi Dan, What happens if you remove the On Error Resume Next statement? It will mask any problems in the code.
Comment by: T Pearce (22-5-2012 14:30:49) deeplink to this comment
I am using slicers in a financial application I created and it works great filtering the data, but when you drill down to see the details, it does not filter the data at all. Can you give me some insight on how to prevent this from happening. I don't understand why it does not filter the detail data.
Comment by: Jan Karel Pieterse (23-5-2012 23:14:45) deeplink to this comment
Hi T., This is one of those situations where a sample file is needed to see what is going on. Please use the email address below to send me one (if possible).
Comment by: katia (25-5-2012 14:24:56) deeplink to this comment
Hi Eileen, I am preparing for my next class. In my Pivot Table -> Options -> Sort, the "Insert Slicer" is not shown at all. Can you help me? Thank you so very much in advance.
Comment by: Jan Karel Pieterse (26-5-2012 02:56:53) deeplink to this comment
Hi Katia, Eileen is not my name :-), it is the name of a forum where you can ask Excel questions. To your question: Do you have Excel 2010? Slicers are not available in older Excel versions.
Comment by: Lizzie (7-6-2012 23:11:14) deeplink to this comment
I have created my slicer and formatted it to match my table but I need to display only months and not the full date so that I can sort my table by month. How do I modify my date so that it only shows the month and not the full date? Thanks
Comment by: Jan Karel Pieterse (8-6-2012 11:58:19) deeplink to this comment
Hi Lizzie, I expect you can do this by first grouping your date field in the pivot table by months and then inserting a new slicer for the month.
Comment by: Hank Buonforte (23-6-2012 01:09:17) deeplink to this comment
Does anyone know how I can get a slicer search bar? I want to be able to type something into the search bar and press enter and have it select what I typed in the slicer.
Comment by: Jan Karel Pieterse (25-6-2012 07:08:25) deeplink to this comment
Hi Hank, Unfortunately, no there isn't one. Would have been a great addition!
Comment by: Anwaar (2-7-2012 06:55:00) deeplink to this comment
Hi When I tried to add more than one pivot table connections to my slicer, I get a message stating "An error occured and the slicer can not currently be created or connected to pivot tables". Why I am I unable to connect? I've just 1 Pivot Cache (checked as per your instructions above. I am facing the same problem as Kajsa-Stina (2/5/2012 11:46:01 PM). I was curious to know if you have found a work around to it instead of re-creating all the tables. Please advise.
Comment by: Jan Karel Pieterse (2-7-2012 11:11:26) deeplink to this comment
Hi Anwaar, Perhaps Excel thinks that the pivot table was created in an older version of Excel (I have seen that happen to files which were created in Excel 2007). Try recreating the pivot table. Also: make sure the file is NOT saved in Excel 97-2003 file format.
Comment by: Steve (5-7-2012 14:25:19) deeplink to this comment
My fiscal year runs from July to June. After creating my pivot table and inserting a slicer for the months, the slicer automatically sorts it from January to December. I cannot find an option to customize the sort order of the slicer. Thanks for any help you can give me.
Comment by: Jan Karel Pieterse (5-7-2012 19:28:37) deeplink to this comment
Hi Steve, Perhaps you can add a custom sort order in the custom lists item in Excel Options?
Comment by: Steve (12-7-2012 16:59:57) deeplink to this comment
Having created a custom slicer style in a workbook, how do you export that style to be used in another workbook?. Thanks
Comment by: 100tsky (16-7-2012 02:15:52) deeplink to this comment
Hi! Can I get different format of measure based on value of slicer items, something like that: If SlicerItem = "abs" Then With ActiveSheet.PivotTables("PP01").PivotFields( _ "[Measures].[sum]") .Calculation = xlNormal .NumberFormat = "# ### ##0_ð_." End With End If If SlicerItem = "ratio" Then With ActiveSheet.PivotTables("PP01").PivotFields( _ "[Measures].[sum]") .Calculation = xlPercentOfColumn .NumberFormat = "0.0%" End With End If thank you!!!
Comment by: Jan Karel Pieterse (7-8-2012 11:35:45) deeplink to this comment
Hi 100tsky, I doubt if you can do that, but you could tie the code you show to two buttons to mimick the behaviour?
Comment by: Jan Karel Pieterse (7-8-2012 11:37:48) deeplink to this comment
Hi STeve, You can copy a slicer with that style to the new workbook and after that delete the slicer (I think).
Comment by: David (15-8-2012 13:22:42) deeplink to this comment
I am trying to use slice, but when I go to set it up the slice icon is dimmed in the tool bar. Am I missing something? How do i get the slice icon to enable? David
Comment by: Jan Karel Pieterse (15-8-2012 18:29:15) deeplink to this comment
Hi David, The pivot table you are trying to connect to a slicer must be in a file create by Excel 2010 and saved in the 2010 file format (xlsb, xlsx or xlsm). Pivot tables created in earlier versions or in files of older Excel versions cannot be combined with a slicer.
Comment by: Meggan (17-8-2012 00:22:42) deeplink to this comment
I have Excel 2010 with Win 7 -- I can neither modify nor delete custom styles (either Pivot Table or Slicer) and it's driving me CRAZY! The options are available for me to click, but nothing happens after I do. Seen it? Got a fix? Thanks!
Comment by: Jan Karel Pieterse (17-8-2012 11:53:06) deeplink to this comment
Hi Meggan, Odd indeed. Perhaps there are slicers/tables that have the custom style applied to them, making Excel refuse to remove the style?
Comment by: Chris (21-8-2012 00:08:12) deeplink to this comment
Okay, when I first saw slicers, I thought, yeah, they're nice in that they look better than pivot table report filters, but that's really all they are - nicer looking pivot table report filters. Then I read this article and discovered that they will apply your hierarchical groupings to your filtering. Okay. That's cool. I think more of slicers now than I did two minutes ago (I'm a slow typer ;-))
Comment by: Carlos (8-9-2012 00:52:17) deeplink to this comment
I have a pivot table in which I use two fields as report filters. The first field is a set of item groups, and the second field is a set of items contained in an item group. The various item groups have differrent sets of items. I would like to set up two slicers (item group, and item) such that when I pick a value in the item group slicer, the item slicer only shows the values appropriate for the selected item group. Can this be done, and if so how. I appreciate your help. Thanks.
Comment by: Jan Karel Pieterse (10-9-2012 10:02:04) deeplink to this comment
Hi Carlos, Slicers offer that option by default, but how they behave when items are not applicable is something you can control in the slicer options dialog, which is accessible through a right-click on the slicer and select "Slicer settings". However, you cannot set it to not show non-applicable items, you can only make sure those are shown at the bottom of the list and in a different color.
Comment by: Jacob Jørgensen (12-9-2012 11:39:17) deeplink to this comment
Hi, I have designed a pivottable, and I want to use the slicer tools. My idea is to use a togglebutton, so if it is activated it will show the slicer, and deactivated it will not appear. Is that possible? Hope you understand. Kind regards Jacob
Comment by: Jan Karel Pieterse (13-9-2012 09:48:16) deeplink to this comment
Hi Jacob, Sure that is possible, but it requires a bit of VBA which you tie to the toggle button (though I would probably a checkbox instead). I added a checkbox from the forms toolbar to a worksheet. The checkbox's name is "Check Box 1". I assigned this macro to the checkbox:
Comment by: Mark (25-9-2012 11:10:26) deeplink to this comment
I am trying to copy a slicer from one workbook to another. The slicer i pasted is no longer linked to the pivotable on the original file i copied from. How can i do it?
Comment by: Jan Karel Pieterse (25-9-2012 13:24:06) deeplink to this comment
Hi Mark, Looks like a slicer cannot be tied to a pivotcache in a different workbook.
Comment by: Michelle (26-9-2012 15:53:51) deeplink to this comment
Hi, One of my slicers contains a significantly long list of names. I realize that if I need to do a search I can do so in the pivot table itself. However, is there a way I can give the user the ability to do this in the slicer? By the way, using Excel 2010. Thanks in advance!
Comment by: Jan Karel Pieterse (26-9-2012 19:15:07) deeplink to this comment
Hi Michelle, I know, that is a PITA. The only way is the one you wrote about.
Comment by: Paul Kelly (2-10-2012 11:47:39) deeplink to this comment
Would like to show what slicers (filters have been applied) e.g. If Customer filter and Joe Bloggs and AN Other have been selected - show at the top the slicer that has been applied.
Comment by: Jan Karel Pieterse (2-10-2012 13:13:21) deeplink to this comment
Hi Paul, Well, of course the pivot table itself does show whether or not filters have been applied and if it is only one item, it shows the item. I seem to recall one of the CUBE worksheet functions can be tied to a slicer, but I have not tried this, nor do I know whether this can be done to any slicer.
Comment by: Rebecca Haack (17-10-2012 20:20:01) deeplink to this comment
Can you get a total for the slicer's selection? For example, If I have the slicer set for January, can I get a total for all the amounts the slicer has selected for January?
Comment by: Jan Karel Pieterse (18-10-2012 11:03:02) deeplink to this comment
Hi Rebecca, The pivot table shows the total for the slicer's selection, provided you have included totals in your pivot table of course.
Comment by: Ricardo Madaleno (18-10-2012 15:04:53) deeplink to this comment
Hi, I have an issue. Whenever I select one of the filters in my slicer, it changes the height of my header rows... Is there a way to fix this? Thanks
Comment by: Jan Karel Pieterse (19-10-2012 12:00:22) deeplink to this comment
Hi Ricardo, Perhaps you can fix this by changing the pivot table's settings regarding keeping formatting and adjusting column widths?
Comment by: Ricardo Madaleno (19-10-2012 13:48:20) deeplink to this comment
Hi Jan, no, that's not it... i have my pivot with the option to autofit unchecked. this is very strange... can't seem to fix it.
Comment by: Jan Karel Pieterse (19-10-2012 19:08:39) deeplink to this comment
Hi Ricardo, If the slicer is filtering the page filter, perhaps the column where the page filter is located is too narrow for certain values If that cell has word wrap on, it will force Excel to increase row height to display the full value.
Comment by: Anne (30-10-2012 05:45:19) deeplink to this comment
Hi! I have been using slicers as a great way to indicate what areas of our business that needs attention. I want to do this once a month. But when I try to update the pivot table/ refresh, nothing changes in the slicers. Help would be much appropriated :) Anne
Comment by: Jan Karel PIeterse (30-10-2012 08:57:14) deeplink to this comment
Hi Anne, There should be an option in the slicer settings that allows you to indicate whether or not to have the slicer include items which are no longer in the pivotcache. Is that what you are after?
Comment by: Anne (30-10-2012 11:04:05) deeplink to this comment
Thank you for your reply. This is the message I get when I try to update my with the slicers; "The data source of a PivotTable connected to the slicers that are also connected to the Pivot Tabel cannot be changed. To change the data source first disconnet the slizers from this and other pivottables". So my question to you is, is there any way to get around this? I would really like to keep my slizers and not have to do them wach time i update my pivottable. Thanks again!
Comment by: Jan Karel PIeterse (30-10-2012 12:06:14) deeplink to this comment
Hi Anne, Odd, I just created a PT bound to an Access database, with one slicer connected to it. If I change the PT source (by editing the connection properties), it just lets me do that without complaining.
Comment by: Oz (13-11-2012 11:05:40) deeplink to this comment
Hi Jan, Like Ricardo I'm having problems with my slicers resizing my pivot table, though mine are changing the column width: -My table is set to not resize on update. -The values in my table are never more than 2 digits. -The row labels are abbreviated month names. -It doesn't matter where on the sheet the slicer is. -It doesn't help if I stretch the columns out to allow some extra room. -Whenever I change the months the row label column resizes. I think it may be seeing the full name of the month even though it shows the abbreviated name, but if I stretch it to allow room for this it'll shrink for shorter month names. Thanks for your help
Comment by: Jan Karel Pieterse (13-11-2012 11:35:51) deeplink to this comment
Hi Oz, Can you perhaps email that workbook to me (pls refer to this page when doing so so I won't discard that email as spam)?
Comment by: Ram (13-11-2012 16:15:34) deeplink to this comment
Hi Jan, Awesome job... I read through every post. Its really a task to keep replying.. But We all truly appreciate. I read through all the post but may have missed this. I have a slicer and a pivot chart which is a combo with line and bar. Whenever I select an option from slicers it changes the line chart back to bar. Can you help to resolve this. Thanks again.
Comment by: Oz (13-11-2012 16:26:01) deeplink to this comment
Hi Jan, Thanks for getting back to me so quickly. I'm afraid I can't send it to you as it contains sensitive company data. I tried to remove all the personal data and give you a version with just the sheets in question, and what do you know; the problem went away! Still there on the original though :(
Comment by: Oz (13-11-2012 16:29:43) deeplink to this comment
Well that's embarrasing; I had a feeling the gentleman who created this workbook might be behind the fault so I looked through the page and found, right at the bottom, another pivot table, with autofit on! Well, at least its solved, and I've got another horror story to share with the wife :) Thanks again Jan
Comment by: Rebecca (13-11-2012 16:34:12) deeplink to this comment
Hi Jan, Did you find a solution to the problem posted by T Pierce on 5/22/12? I am having the same problem. I want to have slicers in a worksheet that are linked to multiple pivot tables and charts, and be able to drill into the details for just the results filtered by the slicers. Thanks!
Comment by: Jan Karel Pieterse (13-11-2012 19:07:52) deeplink to this comment
@Oz: Thanks for letting me know, another mystery solved. @Rebecca: No, but perhaps you can ask your question over at Eileen's lounge? @Ram: I think the only way is to add a bit of VBA code that resets the chart after filtering.
Comment by: Trish McDermott (7-12-2012 00:11:04) deeplink to this comment
Hi there! thanks for a really simple explanation on slicer cache...well done. Now I don't want a message box to appear, but the slicer value to appear in a cell... how can I do this? I'm tearing my hair out! It is probably really simple, I must be missing something. I have one pivot table and 1 slicer in my workbook. Cheers Trish
Comment by: Jan Karel Pieterse (7-12-2012 13:04:56) deeplink to this comment
Hi Trish, Like so perhaps?
Comment by: Mark Fay (7-12-2012 17:55:29) deeplink to this comment
one big problem I can't get past with slicers is that that deleting or modifying a custom slicer does not work. The only way I have of modifying a customer slicer is to duplicate it, the modify box then pops up. I now have 11 custom slicers in various stages and cannot delete any. Is there some trick to get modify and delete to work. The prompt dialogue boxes come up but don't actually do anything.
Comment by: Bill (8-12-2012 23:42:53) deeplink to this comment
Thank you for these explinations. I like the last code snippet you gave to Trish. When combined with a "Worksheet_PivotTableUpdate" Subroutine the code helped me understand the slicer actions better.
Comment by: Bill (9-12-2012 00:25:41) deeplink to this comment
So my question would be... if I have the code you suggested for Trish wrapped in a "Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)" subroutine, how could I have it cycle through and print out information for all of the slicers associated with the PivotTable that was associated with the slicer that activated the macro. Also, I can't seem to get out the "Parent" value for a slicer item. I keep getting "Run-time error '1004' Application-defined or object-defined error". Thanks in advance.
Comment by: Jan Karel Pieterse (10-12-2012 08:29:16) deeplink to this comment
Hi Bill, Actually, it is the pivot table that activates the event routine, not the slicer. Anyway, perhaps this gets you going:
Comment by: Jan Karel Pieterse (10-12-2012 08:33:16) deeplink to this comment
I expect you are referring to a custom slicer style? Some important things to know: 1. You can only modify a custom slicer style. Built-in styles cannot be modified. 2. (and I think this is the one you need!) After duplicating a built-in style and modifying it, you have to apply the new custom slicer style to the slicer in question. Only then do your modifications appear.
Comment by: Mark Fay (10-12-2012 08:56:05) deeplink to this comment
thanks Jan, yes it is a custom style. I can modify after duplicating but then cannot modify it again unless I duplicate again. Also cannot delete it. I want to modify a style check it then make more modifications until I have what I want. Any ideas?
Comment by: Jan Karel Pieterse (10-12-2012 09:00:13) deeplink to this comment
Hi Mark, I can modify them as much as I like after creating a custom one. I can also delete them, even though they are in use on a slicer. Perhaps your worksheet/workbook is protected?
Comment by: Obwan (11-1-2013 05:54:19) deeplink to this comment
Hi I saved my Excel 2010 workbook with 10 pivot tables connected to 1 slicer. Works perfectly. Now I have added additional pivot tables but cannot get them to list in the slicer connections. Is there a limit to the number of connections or is there a trick I am missing. Thank you in advance.
Comment by: Jan Karel Pieterse (11-1-2013 16:30:48) deeplink to this comment
Hi Obwan, The trick is that the pivot tables must all share the same pivot cache.
Comment by: Amir (17-1-2013 18:01:31) deeplink to this comment
Hi I am using pivot tables and slicers to create dashboard reports. The info you have provided on this web page is fantastic and very well put together. I have a large pivot table with fields such as cost centres, cost, date and product code. I have created slicers for cost centres and product code. The individual cost centres are grouped into North Division and South Division. My question is can I create slicers for individual cost centres, all North Division cost centres, all South Division cost centres and finally whole Group cost centres? I hope this makes sense! Would very much appreciate your thoughts or advice.
Comment by: Jan Karel Pieterse (18-1-2013 16:33:52) deeplink to this comment
Hi Amir, No, all you can do is put a slicer up there that filters on Division. The cost center slicer will then (if properly setup) show the relevant cost centers at the top and the other ones greyed out near the bottom.
Comment by: Bob (24-1-2013 09:40:12) deeplink to this comment
Thanks for the really detailed descriptions on how to work with slicers. I would like to add one question: Is there a chance to define which selectable buttons in a slicer are shown? Can you specify a slicer filter to be hidden? e.g.: My slicer shows 4 options "Products active", "Products inactive", "Products to be developed" I would like to hide only provide "Products active", "Products inactive" to be selectable. Best regards Bob
Comment by: Jan Karel Pieterse (24-1-2013 10:19:26) deeplink to this comment
Hi Bob, The only way to do that is to have a separate field in the pivot table's source data which would filter out the item you do not want in the slicer. Then you must set up the slicer to hide non-applicable items and filter the pivot table on the field that affects which slicer items apply.
Comment by: Bob (24-1-2013 18:51:55) deeplink to this comment
Hi Jan, thanks for your quick response. I tried and it may work, but I cannot tell my slicer to not show non-applicable items. The only way I could do it is to define the color scheme for items with no data so that the button will become invisible.
Comment by: Narayan (30-1-2013 07:32:51) deeplink to this comment
Can I connect two slicers (having the same dataset)? My requirement is to change the data in slicer2 depending on the input from Slicer1. Accordingly the data in the pivot tables change.
Comment by: Jan Karel Pieterse (30-1-2013 12:51:52) deeplink to this comment
Hi Narayan, I'm not sure what you mean by "connecting" them, the slicers are connected and do reflect filters of other slicers (of you have set the properties accordingly).
Comment by: Tiana (7-2-2013 05:34:00) deeplink to this comment
I need to go through the slicer items one by one and activate each option so I can save the result as a pdf file. The following code activates each option but when it moves onto the next one it keeps the previous one selected: Sub GetSlicerValues() Dim oSi As SlicerItem For Each oSi In ActiveWorkbook.SlicerCaches("Slicer_cd_merchant_id").SlicerItems oSi.Selected = True Next End Sub Is there a way to select one a time? Thanks, T
Comment by: Jan Karel Pieterse (7-2-2013 20:33:57) deeplink to this comment
Hi Tiane, You could do it like this:
Comment by: Stephen (9-2-2013 00:06:03) deeplink to this comment
Is there any way to save your custom slicer styles so they are available across workbooks?
Comment by: Jan Karel Pieterse (10-2-2013 20:34:21) deeplink to this comment
Hi Stephen, If you copy a slicer with that slicer style applied to a fresh workbook and then save-as that workbook as book.xltx (or m) to your xlstart folder the slicerstyle should become available to each new workbook you start with control+n.
Comment by: Beth (13-2-2013 01:53:52) deeplink to this comment
I am sending out a pivot table to several folks and I'm using my first slicer. Lovely tool. However, I typically protect the sheet to protect those folks from themselves. :) You know, accidentally changing up the columns, etc. When I do that, the slicer stops allowing the user to select a different item, even tho the users of the pivot table can still change the sort or perform additional filtering within the pivot table itself. Is the slicer not considered part of the pivot table when I select my protection options?
Comment by: Jan Karel Pieterse (13-2-2013 08:54:14) deeplink to this comment
Hi Beth, This can be done, but it is tricky: Go to the “size and properties” dialog and uncheck the “locked” property for the slicer, THEN protect the sheet with “use PivotTables” (if you’re using pivot slicer) or “use autofilter” (if you’re using table slicer in Excel 2013).
Comment by: Arjo (15-2-2013 10:19:30) deeplink to this comment
Hi, I use slicers a lot. I used to be able to copy them around my excel file. Today I was able to create a slicer at the pivottable sheet, but I could not copy paste it to my pivotgraph sheet. It was pasted as an image. I also do not have the opportunity to add a slicer when I have the graph selected. Something which normally is possible. Is there something I can do to fix this? I need the slicers a lot for creating a dashboard but I have to be able to move the slicers around my excel file. thanks!
Comment by: Jan Karel Pieterse (15-2-2013 15:53:10) deeplink to this comment
Hi Arjo, I haven't heard of that problem before. Is the chart a chart embedded on a sheet or on a chartsheet? Perhaps chartsheets cannot hold slicers? Perhaps the file/sheet is protected?
Comment by: Arjo (18-2-2013 11:03:13) deeplink to this comment
I am not 100% sure, but I believe that chart sheets cannot hold slicers. I placed the graph on a regular sheet and everything worked fine. Although it would be nice to use slicers with multiple data sheets, I know this is not going to happen any soon. So for this moment I will have to create multiple slicers.
Comment by: Jan Karel Pieterse (18-2-2013 11:54:28) deeplink to this comment
Hi Arjo, What do you mean by using slicers with multiple data sheets?
Comment by: Arjo (18-2-2013 13:28:22) deeplink to this comment
I'd like to connect 1 slicer to pivottables from different data sources. something which is not possible... for example: I have 2 data sources. data sheet 1 and data sheet 2. In both the field 'account' is available and in both the values are the same. I want to have 1 slicer to with which I can filter the account value in all the connected pivottables.
Comment by: Jan Karel Pieterse (18-2-2013 17:07:33) deeplink to this comment
Hi Arjo, Check out this page, in the comments there should be some code to tie two slicers together. https://jkp-ads.com/Articles/slicers03.asp
Comment by: Debbie Leonard (21-2-2013 04:01:32) deeplink to this comment
I have a button that clears all my slicers filters...it works great, but I'd like to add code to have all slicers scroll back to the top of each slicer list. I've been looking for days but no luck finding an answer. I'm using Slicers with Excel 2010, not PowerPivot. Thanks.
Comment by: Jan Karel Pieterse (21-2-2013 09:55:29) deeplink to this comment
Hi Debbie, What you could try is first setting the filter to the first item in the slicer, before removing the filter.
Comment by: Debbie Leonard (26-2-2013 01:17:18) deeplink to this comment
How do I set the filter to the first item in each of my slicers? Thanks, Debbie
Comment by: Jan Karel Pieterse (26-2-2013 08:52:37) deeplink to this comment
Hi Debbie, It took me some digging, but I found a (rather kludgy) way to scroll the slicer to the top. Like so: (note that this also clears the filters)
Comment by: Yan (12-3-2013 06:22:15) deeplink to this comment
Hi, What VBA coding can i use to change my pivot table data source that is connected to slicers? My data will keep increasing and i need to update the data source range every time. my slicers becomes unreadable when i use the following code:
Comment by: Jan Karel Pieterse (12-3-2013 08:39:01) deeplink to this comment
Hi Yan, No need to keep updating the datasource if you point the pivottable to entire columns, or -even better- if you convert the range to a table and point the Pivot table to the table.
Comment by: yan (12-3-2013 11:53:33) deeplink to this comment
Pointing the Pivot Table to the table works very well! Many thanks
Comment by: Arjo (21-3-2013 11:30:57) deeplink to this comment
Regarding using vba and slicers: I'd like to protect wy worksheets with slicers to prevent them from being ruined by users. I have used all the security options excel offers but my wanted solution is not available. My wishes: -slicers can be used to filter -slicers can not be edited or deleted. Given the available options in excel, this is not possible. When my slicers are prevented from deleting (cannot be selected) my slicers don't work anymore: I cannot select another filter option. how can i achieve the desired security?
Comment by: Jan Karel Pieterse (21-3-2013 11:56:18) deeplink to this comment
Hi Arjo, You can check the “disable resizing and moving” checkbox in the “position and layout” menu of the slicer and you get exactly the behavior you’re looking for.
Comment by: Arjo (22-3-2013 16:56:39) deeplink to this comment
Sweet! thanks a lot. tried out a lot but not that one. I think it should be possible to let slicer 1 control pivottable 1 en pivottable 2. (as long as they share the same data source). Is that correct? I have 2 sheets: pivottable food-drug and pivottable food-drug (2). 1 want to have 1 slicer which controls both. Both pivots are bases on the sheet Food-Drug (2). So they have exactly the same field names and values. But when I look at pivottable connections for the slicer, only the pivots at pivottabel food-drug are available to check.
Comment by: Jan Karel Pieterse (23-3-2013 19:41:16) deeplink to this comment
Hi Arjo, Yes, you can do that. Check out the section called "Controlling which pivots are handled by your slicers. " on the previous page of this article.
Comment by: Alex (26-3-2013 23:07:16) deeplink to this comment
Hi Jan, according to Excel 2K10 Help, SlicerItem.Selected is readonly for slicers connected to OLAP data sources. Is there any workaround beside changing the visiblity of the respective PivotItems of the PivotTable in order to programmatically change the SlicerItem-selection? I do programmatically change SlicerItem.Selected for standard (non OLAP) pivots and miss this feature quite heavily as we are using more and more OLAP cubes.. Thanks in advance for any idea or comment! BR Alex
Comment by: Jan Karel Pieterse (27-3-2013 15:36:22) deeplink to this comment
Hi Alex, In that case I guess all you can do is use the pivotitems visibility indeed, no other option as far as I can see.
Comment by: Arjo (29-3-2013 10:20:29) deeplink to this comment
is there a function or script that automatically (re)connects (all) slicers in a workbook, to the pivots that are available for these slicers?
Comment by: arjo (29-3-2013 10:33:26) deeplink to this comment
continuing on my question of 3/22/2013: I looked at the mentioned explanation. this unfortunately didn't answer my question. Because the pivot table I'd also connect to the slicer is not available in the pivottables that are shown in the list of pivottables. Both have the same data source... i have pivot 1 which is connected to food-drug2 and i have pivot 2, which is on another worksheet, which is also connected to food-drug2 but somehow, my pivot2 isn't shown in the available pivots to connect to the slicer.
Comment by: Jan Karel Pieterse (29-3-2013 11:45:53) deeplink to this comment
Hi Arjo, I have no "ready-made" code available. I could write some code for you commercially if you like?
Comment by: Jan Karel Pieterse (29-3-2013 11:49:56) deeplink to this comment
Hi Arjo, If 2 pivot tables are connected to the same data source, that does not necessarily mean they share the same pivot cache. You can create pivot tables on the same data source in two distinct ways: 1. By having both tables share the same cache. 2. By giving each PT its own cache. The first method saves space, as the data source is only loaded once for both tables together. The second method enables you to have different grouping for each pivot table independently. If you use the Insert, pivot table button Excel defaults to option 1. If however you use the pivot table wizard, Excel defaults to option 2. Easiest is to recreate the second PT by using the wizard and selecting Another Pivot table as its source.
Comment by: ana (3-4-2013 13:03:44) deeplink to this comment
How do I change the order of the buttons in a slicer? I have A, B, C, and D buttons and i want to arrange them like C,B,D, A.
Comment by: Jan Karel Pieterse (3-4-2013 13:19:22) deeplink to this comment
Hi Ana, I don't think you can change the order. I tried with an existing pivot table, but failed. In the properties of the slicer you can set the sort order to be the same as in the data source. Perhaps that helps?
Comment by: Marco (6-4-2013 03:24:03) deeplink to this comment
Dear All, Just a question about a matter that it's not clear for me. IF I have two different Pivot generated by two differents DB but these Db are some fields in common. If i considere one of these field It's possible to use just one slicer to connect both pivot? Just to be more clear I make an example: DB 1: Fields: A B C (possible values for the field A: 0,1,2) DB 2: Fields: A and E (possible values for the field A 0,1,3) is it possible to use only one slicer to control the field A in both DB (maybe with possible values for A 0,1,2 and 3)or I have to create necessarily two different slicers? If it's possible also without VBA? I hope my question is clear Awaiting for your answer, please let me know Thanks Marco
Comment by: Jan Karel Pieterse (7-4-2013 16:35:07) deeplink to this comment
Hi Marco, No, you cannot. The two pivot tables must use the same pivot cache, which means at least they must use the same source (range).
Comment by: Martina (9-5-2013 17:52:31) deeplink to this comment
Hi, I saw someone asked the same question but i can't find the answer. I have a slicer and a pivot chart which is a combo with line and bar. Whenever I select an option from slicers it changes the line chart back to bar. Can you help to resolve this?? Thanks
Comment by: Jan Karel Pieterse (10-5-2013 12:14:53) deeplink to this comment
Hi Martina, I expect you can only achieve that by writing VBA code.
Comment by: martina (10-5-2013 18:23:30) deeplink to this comment
Any examples on how to write this VBA code?
Comment by: JoeJoe (10-5-2013 18:39:37) deeplink to this comment
I have pivot table with Name sort in order of "Z, D, H, I, F" ; a custom order. When I do slicer, I ask for "use custom list when sorting". but there is no way to disable the radial dial for "ascending" / "decending"; so the items all listed different from the above order. How can I have slicer item listed is a specific order?
Comment by: Jan Karel Pieterse (13-5-2013 11:41:21) deeplink to this comment
Hi Martina, The easiest would be something like this: - Change the slicer filter - Start recording a macro - Correct the chart so it displays how you like it - Stop recording - Put a button on the sheet that holds the slicer, which you attach to the recorded macro. - Instruct user to push button after changning filter. It is possible to run a macro after a pivot table has been refreshed automatically, but it is a bit harder to explain :-)
Comment by: Jan Karel Pieterse (13-5-2013 11:47:37) deeplink to this comment
Hi JoeJoe, Odd. I just tried with a short table containing only the letters you mentioned, each in a separate cell in the order you mention. I added that table to my custom sort lists. Then I created a pivot table based on the same list and added that field as a slicer. The order was identical to the order of the list and changing the Ascending to descending just reversed the order, as expected.
Comment by: Martina (16-5-2013 23:14:09) deeplink to this comment
I used a modified version of your recording a macro idea and it worked out nicely! Thank you!!!
Comment by: Gary Gray (28-5-2013 14:34:26) deeplink to this comment
I want to insert a slicer from a database of over 1000 items but only want to include 11 items from that list in my slicer, is that possible if so how?
Comment by: Jan Karel Pieterse (28-5-2013 15:45:24) deeplink to this comment
Hi Gary, The only way I can see you do that is by filtering the database on the field that determines which items to show. Even then, the slicer will show the other values, but the ones that do not apply are colored in a lighter fill.
Comment by: JHN (30-5-2013 15:16:07) deeplink to this comment
Hi, Trying to control more slicers with different data sources, which works fine when I push selected values from slicer1 to slicer2. It also works when I push selected data from slicer2 to slicer1, except if I have more values in the slicer I'm pushing values to. If there are more sliceritems in slicer they stay visible, but should be turned off. Any ideas? Dim mbNoEvents As Boolean Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable) Dim oSl As SlicerItem If mbNoEvents Then Exit Sub On Error Resume Next 'IN case a slicer item in slicer 1 is not present in slicer 2 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual '*****Push belt series 1 to 2***** If Sh.Name = "Piv BPL" Then mbNoEvents = True ActiveWorkbook.SlicerCaches("Slicer_Belt_series1").ClearManualFilter With SlicerCaches("Slicer_Belt_series") For Each oSl In .SlicerItems 'If SlicerCaches("Slicer_Belt_series1").SlicerItems(oSl.Caption).Selected <> oSl.Selected Then SlicerCaches("Slicer_Belt_series1").SlicerItems(oSl.Caption).Selected = oSl.Selected 'End If Next End With End If '*****Push belt series 2 to 1***** If Sh.Name = "Piv FL" Then mbNoEvents = True ActiveWorkbook.SlicerCaches("Slicer_Belt_series").ClearManualFilter With SlicerCaches("Slicer_Belt_series1") For Each oSl In .SlicerItems If SlicerCaches("Slicer_Belt_series").SlicerItems(oSl.Caption).Selected <> oSl.Selected Then SlicerCaches("Slicer_Belt_series").SlicerItems(oSl.Caption).Selected = oSl.Selected End If Next End With End If Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic mbNoEvents = False End Sub
Comment by: Jan Karel Pieterse (30-5-2013 16:53:30) deeplink to this comment
Hi JHN, All I can come up with is that you must set all sliceritems of the "other" slicer to unselected before you set the slicer items to the same selected value. So two loops per sheet.
Comment by: JHN (31-5-2013 10:18:34) deeplink to this comment
Hi Jan, Thanks for your feedback. Do you have any suggestions on how to unselect all sliceritems. I tried with: Dim SC1 As SlicerCache Set SC1 = ThisWorkbook.SlicerCaches("Slicer_Belt_series") Dim item1 As SlicerItem For Each item1 In SC1.VisibleSlicerItems item1.Selected = False Next item1 but when the last sliceritem is unselected, all is automatically selected again ... as the slicer can not be "empty" Any suggestions? Thanks in advance
Comment by: Jan Karel Pieterse (31-5-2013 12:00:56) deeplink to this comment
Hi JHN, I'm sorry, I forgot that you need to keep at least one item selected :-( One way around it is rather than using a for each loop, you use a counted loop starting at item # 2 after making sure item # 1 is selected. You'll have to adapt the code so that it checks whether Item # 1 actually needs to be selected during the second phase (where you match up the selections of both slicers) and then adjust the selected mode of the item afterwards if necessary.
Comment by: JHN (31-5-2013 14:16:27) deeplink to this comment
Hi Jan, Any suggestions about how such a code could look like? thanks in advance
Comment by: Jan Karel Pieterse (31-5-2013 16:15:22) deeplink to this comment
Hi JHN, Something like this:
Comment by: JHN (5-6-2013 21:08:16) deeplink to this comment
Hi Jan, thanks for your feedback ... we are alsomst there (99%). Your script will always leave the first slicer item selected in slicer2, when selecting something in slicer1 which is not present in slicer2. I have spent hours trying to solve it myself without success. Any more ideas? Would it be possible when selecting an item in slicer1, first checking if it exist in slicer2, if exist sub, if yes run script? ... and how could the checking part look like?
Comment by: Wayne (17-6-2013 19:36:25) deeplink to this comment
I have a pivot table/chart that I have connected to 4 slicers. The slicers are for different levels of data on the chart: District, manager, lead-office, and office. When a district (manager, etc) is selected in a slicer, I want the other slicers to grey out members that do not belong in the selected grouping. I have one column label field in pivot that includes some calculated items. All of this works until one of the calculated items is selected in pivot, then all the slicers lose their ability to grey out non-selected groupings. When calulated items are deselected, this ability returns. Column label field selections are done through combo boxes and VB code. This is limit choices to desired items. Slicer fileds are all in the report filter section of pivot. Data is MS query cannection to an Access database. Calulated items are ratios. There is one row label field. Data query is very large(over 1/2 million rows). I need slicers to keep filter ability even with calculaed items. Is there a property I can set to keep filters working on calculated items? Or another way to get the desired filtering to work? Any ideas?
Comment by: Jan Karel Pieterse (17-6-2013 19:55:45) deeplink to this comment
Hi Wayne, I would try to move the calculation form the pivot table to the query.
Comment by: Wayne (17-6-2013 20:23:47) deeplink to this comment
Calculations are ratios and can't be calculated until after level is selected.
Comment by: Jan Karel Pieterse (18-6-2013 10:32:17) deeplink to this comment
Hi Wayne, In that case I guess you are limited to what Excel delivers, which isn't very useful in this case!
Comment by: Malinda (27-6-2013 17:08:04) deeplink to this comment
I'm using exel 2010. File saved in xx.xlsx . No slicer. No options is displaced in sort & filter option. Tried a solutions to fix this in wed..... Nothing works....pls help....
Comment by: Jan Karel Pieterse (28-6-2013 11:52:04) deeplink to this comment
Hi Malinda, You do have a cell selected in a Pivot table?
Comment by: Hansen (15-7-2013 12:40:47) deeplink to this comment
Hi there, I do have multiple slicers all in relation (same source data), and I’m trying to do is following: Each time one selection is done in a slicer, I would like to count remaining selectable items in each remaining slicers . I have tried Visiblesliceritems, but that is only working in the slicer where the selection is made, in all other slicers the function will just count all items. Sub test101() Dim SLC1 As SlicerCache Dim SLI1 As SlicerItem Set SLC1 = ThisWorkbook.SlicerCaches ("Slicer_MBBE_Belt_pitch") Range("MBBE_pitch_01") = SLC1.VisibleSlicerItems.Count Set SLC1 = ThisWorkbook.SlicerCaches("Slicer_01_MBBE_BPL_MBBE_Belt_series") Range("MBBE_series_01") = SLC1.VisibleSlicerItems.Count End Sub Any ideas?
Comment by: Shweta (2-8-2013 08:52:45) deeplink to this comment
i read above lots of people facing difficulties in deleting a slicer setting...i just found a solution!! ..i had lots of protected sheets in my file...i had to unhide all the sheets in the workbook (even the very unhidden ones) and UNprotect them all ...i had to also UNprotect my workbook BINGO!! ...post this i was able to modify / delete my slicer settings!! :-)
Comment by: Jan Karel Pieterse (13-8-2013 07:39:30) deeplink to this comment
Hi Hansen, Sorry for the belated response! I expect you need the Selected property of the sliceritem to determine whether the item is filtered or not and count those.
Comment by: Rebecca (14-8-2013 18:26:11) deeplink to this comment
Hi, Can you tell me if it's possible to link a specific cell to the sliceritem? For example, let's say cell A8 is value "Simon" ... How can I get the slicer to look at cell A8 and automatically filter it on "Simon"? Do I have to call this up in the pivot table vs slicer? If so, how do I do this? thanks in advance ...
Comment by: Jan Karel Pieterse (15-8-2013 08:13:30) deeplink to this comment
Hi Rebecca, When the pivot table is created from powerpivot data you can use the CUBE functions to extract what filter was applied. But without VBA I see no way of doing the opposite.
Comment by: Jeff Weir (6-9-2013 05:00:11) deeplink to this comment
Hi Jan Karel. In your diagram, you have the SlicerCache object tied to the pivotCache object. But what's weird is that given a particular SlicerCache object, there's no direct way you can tell what PivotCache it 'belongs' to, as far as I can tell. Instead, you have to find out the PivotCache of one of the PivotTables that the slicer is connected to, like this: ? sc.PivotTables(1).PivotCache.Index ...And give a PivotCache object, you can't see what SlicerCaches are 'attached' to a PivotCache, unless you iterate through each PivotTable. So while a SlicerCache certainly sits under a PivotCache in terms of what a SlicerCache can control, I'd be inclided to put a dotted line in your above diagram, to show that as far as writing VBA code, you can't directly address one via the other. Your thoughts?
Comment by: Jan Karel Pieterse (7-9-2013 16:26:40) deeplink to this comment
Hi Jeff, You're probably right, I didn't quite check all my assumptions :-)
Comment by: Nicolas Bransier (25-9-2013 01:54:09) deeplink to this comment
Hi Jan I'm using a powerpivot and created pivot charts from there. The pivot tables associated to the charts are not visible to me. How can I run a macro each time I change a slicer value. In your example below, I see that you use Private Sub Workbook_SheetPivotTableUpdate. But in that case, there are no pivot table, only charts. Thanks in advance
Comment by: Jan Karel Pieterse (25-9-2013 09:26:20) deeplink to this comment
Hi Nicolas, As far as I know, a pivot chart *allways* has an associated pivottable, regardless whether the pivotchart was added from powerpivot or form Excel.
Comment by: Nicolas Bransier (25-9-2013 16:58:12) deeplink to this comment
Hi Jan That's what I thought, but how do i identify the name of the pivot table associated to these power charts, they are not visible to me. I aasume all I need is there names so I can use Private Sub Workbook_SheetPivotTableUpdate to implement my code.
Comment by: Jan Karel Pieterse (25-9-2013 17:05:15) deeplink to this comment
Hi Nicolas, If you put a breakpoint in the event code and then make the pivotchart update it should trigger the event and then you should be able to extract the name of the associated pivot table from the Target object within the event?
Comment by: Rick Freitas (27-9-2013 05:09:57) deeplink to this comment
Thank you again for this site, appreciate you sharing your knowledge with the rest of us! Question I need help with please. I have a date slicer that is connected to multiple pivots (external sources thru powerpivot) and I am trying to vba code that upon opening of workbook sets the date to latest upon refresh of pivots. Code is below: Private Sub Workbook_Open() Dim item As SlicerItem Dim temp As Date temp = Format$("9/25/2013", "d mm yyyy") '' Set the oldest date ' Find the latest date. For Each item In ThisWorkbook.SlicerCaches("Slicer_CalendarDate").SlicerItems If item.Name > temp Then temp = item.Name End If Next item For Each item In ThisWorkbook.SlicerCaches("Slicer_CalendarDate").SlicerItems If item.Name = temp Then '' Compare item item.Selected = True Else item.Selected = False End If Next item ThisWorkbook.RefreshAll End Sub So issue is I am getting "run-time error '1004': application-defined or object defined error" on the For Each line right below the commented "' Find the latest date." line (line 6). I know I am using right slicer name as due to going to slicer settings and seeing 'name to use in formulas' says "Slicer_CalendarDate", but if I run the code in a previous post above on getting slicer values, for slicer name it says : [SalesDateViewMonth].[CalendarDate].[CalendarDate]. I have tried using that name as well, but still gives an error, maybe not using right syntax. Anyways, any thoughts you have on what might be going on would be greatly appreciated. Most likely I am missing something simple, I am not much of a VBA coder, but trying. :) Thank You! Rick
Comment by: Jan Karel Pieterse (27-9-2013 11:15:00) deeplink to this comment
Hi Rick, Try if moving the code to a sub in a normal module works. If it does, put a call tot that sub in Workbook_Open, preferably by using:
Comment by: JHN (14-10-2013 15:24:24) deeplink to this comment
Hi, I have been trying to write a macro which count sliceritems left with data. Ex: Slicer1, Slicer2 and Slicer3 all contains 10 items, and by selecting 1 item in slicer1, only 2 items are left in slicer2 and 5 items are left in slicer3. I now want to count the numbers of items in slicer2 and slicer3 and write the result in cell B1 and C1 for later use. Any suggestions how a code could look like? JHN
Comment by: Jan Karel Pieterse (15-10-2013 10:09:20) deeplink to this comment
Hi JHN, This function returns a list of selected items for a slicer passed to the function. I assume you can modify this to fit your needs:
Comment by: Iza (16-10-2013 10:16:55) deeplink to this comment
I would like to set manually order of the list items inside slicer...at the moment I can only see the option of alphabetical order under "slicer settings"....is there any possibility to set them manually in the order I want? thanks for yoru answer, Iza
Comment by: Jan Karel Pieterse (16-10-2013 10:57:15) deeplink to this comment
Hi Iza, If you don't set it to be sorted, it will use the order as they appear in the data, but that does not allways seem to work somehow. So I'm afraid I am unable to answer your question!
Comment by: Rachit (23-10-2013 00:56:46) deeplink to this comment
Hello there, Excel is not letting me refresh a pivot if there is a slicer is attached to it...is there a way to work around this? Regards, R
Comment by: Jan Karel Pieterse (23-10-2013 15:53:16) deeplink to this comment
Hi Rachit, Something else must be wrong here, because a slicer should not prevent a PT from refreshing at all.
Comment by: Andrew (26-10-2013 21:05:08) deeplink to this comment
For those people who cannot modify or delete slicer styles, a simple solution. If ANY of the worksheets in your workbook are protected, the modify and delete commands don't work. It seems like a bug, but it's easy enough to work around. Hope this helps.
Comment by: Kathy (2-11-2013 01:11:43) deeplink to this comment
I have a list of records. Each record has a date. I want a slicer that selects records with dates which fall in a range between two dates. How can I do that.
Comment by: Jan Karel Pieterse (2-11-2013 20:52:27) deeplink to this comment
Hi Kathy, I'm not sure I understand what you want to achieve?
Comment by: Greg (5-11-2013 23:15:28) deeplink to this comment
Kathy, create a slicer against the date field. On the slicer, select the date for the beginning of the range. While holding the <shift> key, select the date for the end of your desired range. It will select records w/dates between the two dates. (holding <cntl> will allow you to select/de-select noncontiguous selections)
Comment by: Kathy (8-11-2013 01:10:34) deeplink to this comment
We actually have excel files tied to a SQL database and are creating pivot tables off the database. I want to put a slicer on those pivot tables. So we want the slicer to look ahead -- that is I want to set it up to find records that haven't been created yet. We have a weekly report and on Monday I want the slicer set to capture all events entered thru the end of the week. That is why we want to create a date range. But the slicers will only let me select dates for which records exist. Any ideas?
Comment by: Jan Karel Pieterse (8-11-2013 19:35:19) deeplink to this comment
Hi Kathy, Not sure why you'd do that, a slicer lets you filter data of a PT, so there islittle point in showing entries which aren't in its source data.
Comment by: Arjo (18-11-2013 09:49:03) deeplink to this comment
I've use the first sub (to enumerate all the slicer caches in a workbook) to find all the slicer caches. Nothing happens though when I execute the sub. Í'm not able to connect slicers to any pivot So i wonder what I have to do to change the slicer caches to the same sourche as the pivots. (to be able to connect the slicers again.
Comment by: Jan Karel Pieterse (18-11-2013 13:13:15) deeplink to this comment
Hi Arjo, You mean it doesn't even show any message boxes?
Comment by: Arjo (18-11-2013 13:46:58) deeplink to this comment
nope. i think there was something wrong with the file. I've copied another file to test and used the adjusted code below to find the names of the caches and their location:
Comment by: Arjo (29-11-2013 11:48:33) deeplink to this comment
Hello, does the slicer also have the option (in vba) to be Always scrolled to the top of the slicer? i'd like to do this for the slicers I use
Comment by: Jan Karel Pieterse (29-11-2013 17:32:40) deeplink to this comment
Hi Arjo, Click on this link and look for a sub named "SetSlicerToFirstItem": https://jkp-ads.com/Articles/slicers03.asp?AllComments=True
Comment by: Jody (17-12-2013 18:00:00) deeplink to this comment
Is it possible for a pivot chart to have a text box or header which lists the option chosen in a slicer? For example, slicer lists different products sold A, B C,etc., chart lists # of products sold, can chart include the name of the products choosen in the slier box in its heading?
Comment by: Jan Karel Pieterse (17-12-2013 18:16:44) deeplink to this comment
Hi Jody, Not easily. If the PT is built off of a powerpivot model then you can use the CUBERANKEDMEMBER function like so (array-entered into as many cells as needed): =CUBERANKEDMEMBER("PowerPivot Data",Slicer_ShipCountry1,ROW()) And then make sure that table is nicely located next to your chart.
Comment by: Sarah (18-12-2013 22:53:31) deeplink to this comment
I tried using the code above to set the slicer to the first item, but I'm not able to get it to work... I have the macro assigned to a command button, but when I click on it, I get this error code: Run-time error '-2147024809 (80070057)': The item with the specified name wasn't found I'm so lost and would gladly welcome your help! Below is the code that had been previously posted in the comments.
Comment by: Jan Karel Pieterse (19-12-2013 09:47:54) deeplink to this comment
Hi Sarah, No idea what is wrong. Have you refreshed your pivot table?
Comment by: Shankar M (13-1-2014 11:07:48) deeplink to this comment
Hi, is it possible to get the results of slicers selection either as a msgbox or as a text box? If yes, can you pls help with it. For example: referring to your example of slicer - if the Shipcountry is selected as "Austria" it should display the result at the bottom of the table/chart. Thanks, Shankar
Comment by: Jan Karel Pieterse (13-1-2014 11:43:54) deeplink to this comment
Hi Shankar, See this page: https://jkp-ads.com/Articles/slicers03.asp There is a comment from me that shows a function called "GetSelectedItemsOfSlicer". You can use that function to get the selected items.
Comment by: Kelvin (18-1-2014 14:33:13) deeplink to this comment
Excel 2010 default slicer layout is vertical. How to change to horizontal slicer? Thanks
Comment by: Jan Karel Pieterse (19-1-2014 12:44:11) deeplink to this comment
Hi Kelvin, I'm not sure what you men by a horizontal slicer. DAre you referring to the timeline slicer? This slicer type was introduced with Office 2013 and has no Office 2010 equivalent I'm afraid.
Comment by: skvg (23-1-2014 15:37:23) deeplink to this comment
hi Kelvin, I think you are trying to have the display horizotally, right? right click on the slicer, select size & properties, position and layout and increas the number of columns. this will arrange the items in the slicer horizontally into the number of columns
Comment by: David (24-1-2014 05:38:20) deeplink to this comment
Am I able to add a "select all" to the slicer instead of holding down the control key? Thanks
Comment by: Jan Karel Pieterse (24-1-2014 07:06:00) deeplink to this comment
Hi David, The control that removes the filter is in fact the same as select all.
Comment by: David (24-1-2014 07:15:33) deeplink to this comment
Thanks Jan -
Comment by: T Ugot (31-1-2014 12:58:13) deeplink to this comment
Dear Jan, I created a pivot table on Excel 2010 and I noticed the field list also generates provisions for Month, Quarter and Year (i think it pulls this from the date column in the source table with format e.g. 2 Jan, 2013). This makes it easy to create a slice with the year and dates. The problem is, I often get feedback form users that the slicers have 'disappeared' on refresh of the pivot table. I realise it's because the month, quarter and year are now missing from the field list. How do i reverse this please? Thanks, T
Comment by: Jan Karel Pieterse (31-1-2014 14:06:22) deeplink to this comment
Hi T, This is probably because there are records in the source without a date entry in them. If that happens, the date grouping is removed.
Comment by: T Ugot (31-1-2014 15:02:16) deeplink to this comment
Hello Jan, I realised this and had corrected the date errors. I was hoping a refresh of the pivot table would correct bring back the months and Year. I even created another pivot table from the same report but the field list still did not contain the options for month and year. Please advise! Thanks, T
Comment by: Hi T, (31-1-2014 17:13:28) deeplink to this comment
YOu will have to re-group the date field to get the year and month back. Alternatively, just add a calculated year and month column to your pivot table's source table. Make sure the formulas in question cater for missing dates (otherwise the year will show as 1900)
Comment by: Megan (6-2-2014 17:44:46) deeplink to this comment
Hello, I am trying to run a macro when a slicer is selected (or my pivot table associated with the slicer is filtered) and I am new with VBA. I thought the code you gave someone above was similar to what I needed but when I used it and modified it for my workbook, nothing happens. Basically, I need a macro that causes a different pivot table to filter, once I filter with the first pivot table providing the illusion that both pivot tables are linked to the one slicer. Can you tell me what I am doing wrong or help me write a better solution?
Comment by: Jan Karel Pieterse (6-2-2014 19:30:03) deeplink to this comment
Hi Megan, I'm not sure what your macro si supposed to do. Is it trying to change the value of a cell that is in fact a pivot page field? That is not how you change a pivot filter. There is code on this page for synching two pivot tables, click https://jkp-ads.com/Articles/slicers03.asp?AllComments=True and look for it.
Comment by: IAN JACKSON (10-3-2014 16:08:07) deeplink to this comment
I have numerous pivot table charts with clustered columns which reflect a list of upto 10 numbered variables, each is a different error code. I want the columns reflecting each error code to have a fixed colour but I dont seem able to achieve this. When the pivot tables are amended with additional data for a new month the columns change colour.
Comment by: Jan Karel Pieterse (10-3-2014 17:38:19) deeplink to this comment
Hi Ian, Unfortunately, PivotCharts don't always retain formatting modifications when they are refreshed. What is retained depends on the Excel version you are using. Older versions retain less and less. Your best bet is to record a macro whilst setting up everything so you can run the macro after a refresh.
Comment by: Jon Acampora (13-3-2014 20:39:20) deeplink to this comment
Great technique, thanks for sharing! It's probably obvious to some, but you might want to mention that you will need to hide the additional slicers or move them out of view. The Selection Pane can be used to Show/Hide the duplicate slicers, and it is also a good place to see a listing of all the slicer names.
Comment by: Jeff Weir (19-3-2014 03:01:26) deeplink to this comment
Hi Jan Karel. Nice work. I wrote some code over at the Contextures blog some time back for syncing pivots that set up a temp slicer to sync them if they shared the same cache, and otherwise used a Dictionary to store just the visible items of the ‘master’ field in that dictionary, so that those settings could be applied again and again to the various ‘slave’ pivots on other caches. Using a Dictionary approach on pivots with different caches was very fast in the case that you had thousands of PivotItems in your PivotFields and multiple pivots to sync, as you only had to iterate through the master field the one time to record just the visible items, and then could clear the slave field and then just hide any items in the slave that were NOT in the dictionary. Code is at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/ if you’re interested . Or a dictionary-only approach at http://blog.contextures.com/archives/2012/08/28/update-specific-pivot-tables-automatically/ That code’s due for a refresh, as the bit that set up slicers (for pivots on the same cache) errors out if the user has already set up a slicer. Plus it strikes me that I can make it even faster if say I’ve got 3 pivots that share cache A and 3 pivots that share cache B, by joining up all the pivots in Cache A with slicers, then joining up all the pivots in cache B with slicers, then syncing just one pivot on cache A with just one pivot on cache B and let the slicers sync the rest. Will work it up and post over at DDOE in due course.
Comment by: praveen (21-3-2014 13:27:19) deeplink to this comment
Hi Folks, how to deselect slicer items without using loops. i want to deselect at a time all slicer items. If anybody know, please help me thanks for advanced.
Comment by: abdul1987 (7-4-2014 14:43:21) deeplink to this comment
how to deselect and select only required items form slicer through VBa code
Comment by: Jan Karel Pieterse (7-4-2014 15:15:50) deeplink to this comment
Hi Abdul1987, The next page shows how to select items in a slicer, perhaps it gives you enough information to solve your problem? https://jkp-ads.com/Articles/slicers04.asp
Comment by: Jamie M. (8-4-2014 21:09:13) deeplink to this comment
Your UDF solution for showing slicer selections is AWESOME...thank you so much!
Comment by: Paul Ranschaert (24-4-2014 17:40:06) deeplink to this comment
Hi, I've created a dashboard with 4 different line graphs showing performance in the last 24-36 months. Every pivotchart is connected with a slicer. The slicers contain about 100 different kpi's. If for example I would like to review the performance for perfect customer orders I select pco and the underlying kpis being the performance on on time deliveries, in full deliveries and availabilty. I also have other combinations but this takes very long every time I need to scroll down the slicers. To make it easier for the users I would like to add macro buttons with pre-defined combinations. I've recorded a macro but when I run the macro it takes ages because the pivottable is re-freshed for every kpi in the slicer. Does somebody have a solution to speed up this process? Thanks, Paul Sub PCO_2() ' ' PCO_2 Macro ' ' With ActiveWorkbook.SlicerCaches("Slicer_KPI1") .SlicerItems("PCO (Perfect customer Order)").Selected = True .SlicerItems("Accident Frequency").Selected = False .SlicerItems("Actual traded (hl)").Selected = False .SlicerItems("Annual Personnel Expense / SC FTE").Selected = False .SlicerItems("Asset Turnover (COGS)").Selected = False .SlicerItems("Asset Turnover (Revenue)").Selected = False .SlicerItems("Asset Utilization").Selected = False .SlicerItems("Asset Utilization Bottling").Selected = False .SlicerItems("Asset Utilization Brewhouse").Selected = False .SlicerItems("Asset Utilization Canning").Selected = False .SlicerItems("Asset Utilization Kegging").Selected = False End With End Sub
Comment by: Jan Karel Pieterse (28-4-2014 10:06:59) deeplink to this comment
Hi Paul, Does it help to add this as the first line of your macro:
Comment by: Paul Ranschaert (28-4-2014 13:22:13) deeplink to this comment
Hi Jan Karel, thanks it helps but it is still slow, with the above mentioned code it takes about 45 seconds and without it takes about 75 seconds. When I run it without the code, I see the following messages: Running Slicer Operations and Calculating Pivottable for every slicer item. Probably with a couple of slicer items this would be a good solution but with the number of items I have it's too slow.
Comment by: Jan Karel Pieterse (8-5-2014 08:36:11) deeplink to this comment
Hi Paul, What if you turn the PivotTable's ManualUpdate to True in your code?
Comment by: Tarik Bazzy (8-6-2014 18:37:20) deeplink to this comment
Hi, I am working on a project at work where I am looking at the same data for multiple months, each month is its own file and contains identical tabs. I created a new file to use as my dashboard, with the tabs 1) Dashboard, 2) Jan Pivots 3) Feb Pivots, etc... I started with the original data sets and created three pivot tables for each months data (Date, Grade, Yes/No). I then copy and pasted them into my new file's corresponding tab (all three jan pivots were pasted in my jan pivots tab.) I have linked all of the cells on my Dashboard tab through VlookUps to my other tabs. Now I would like to use a slicer (if possible) to update every months data on my Dashboard. The problem I am running into is that each pivot table if I were to try and add a slicer to it only recognizes one connection (to its own pivot table). Is there an EASY uncomplicated way (I have no clue how to do anything with code or VBAs) of using slicer to update my dashboard? Hopefully I have included all the details necessary to identify where I may be going wrong or creating problems for myself with the way I set it up? I don't know any help would be greatly appreciated.
Comment by: Jan Karel Pieterse (10-6-2014 08:42:43) deeplink to this comment
Hi Tarik, Can't your data be combined into one larger table so you can draw pivottables off of that? Your current setup sounds overly complex.
Comment by: Kuldip Mond (20-6-2014 11:17:46) deeplink to this comment
Hi I having difficulty trying to cycle through the slicers to see if they are filtered or not. My goal is to get all the selected Slicer into a worksheet so that I can apply a (High to Low) filter to the underlying pivot data so that I can pick the "Top 5 over budget" based upon the selections made in the Data Slicers. I have the following code but get see error: ‘Run Time Error 438’ Object doesn’t support this method’ Can someone advise how I can achieve this.
Comment by: Jan Karel Pieterse (20-6-2014 11:42:49) deeplink to this comment
Hi Kuldip, You declared oS1 as SlicerCacheLevel, but the for loop runs through the SlicerCaches collection, thus requiring an object variable of type SlicerCache.
Comment by: Kuldip Mond (20-6-2014 12:03:24) deeplink to this comment
Still confused, so how should that line of code read ? or do I need to redesign the entire loop. I don't think I am fgar off but don't think I have the ncessary knowledge to fix this. I am trying hard though :-( Thanks in Advance Kuldip
Comment by: Jan Karel Pieterse (20-6-2014 13:23:28) deeplink to this comment
Hi, Simply replace Dim oSl As SlicerCacheLevel with Dim oSl As SlicerCache
Comment by: Zachary Bass (2-7-2014 05:55:02) deeplink to this comment
Hi There: Is there any way to speed up the slicer selection below. I have a very small pivot table with about 200 slicer items, but it takes along time to update the pivot table as the code manages each slicer item. Thanks for any help. Zachary
Comment by: Jan Karel Pieterse (3-7-2014 08:07:49) deeplink to this comment
Hi Zachary, I'm afraid I could not find a way to speed that up!
Comment by: Rafael Lepra (14-7-2014 21:58:06) deeplink to this comment
Is it possible to unselect all the SlicerItem for a particualr Slicer? I tried to do it but it seem that there is a requirement to have at least one item selected, because as soon as the macro sets the Selected property of the last item to False, all the slicer items are selected again. Is there a way to turn off this requirement? I searched but I sould not find a way to do it. Even if you go to the drop down filter on the pivot table, if you unselect all, the OK button is greyed out. Thank you in advance for your help.
Comment by: XQR (15-7-2014 02:39:21) deeplink to this comment
Jan: I want to show the text(such as tooltips) by selecting the slicer member which is a number. I have a lookup list(less than 100), layout as number, text (80 Health). How I can show the "Health" when I hover into "80". Please have a piece of sample code and let me know where the code should be saved into. Thanks in Advance.
Comment by: Jan Karel Pieterse (16-7-2014 16:32:39) deeplink to this comment
Hi XQR, Please ask your question here: www.eileenslounge.com
Comment by: Jan Karel Pieterse (16-7-2014 16:34:14) deeplink to this comment
Hi Rafael, No, you cannot unselect all items, just as you cannot uncheck all items in the usual pivot filter.
Comment by: David H. (24-7-2014 19:25:49) deeplink to this comment
Really enjoy learning about more indepth functionality that can extracted from Excel. Thanks for sharing. Could you provide UDF code that would put the data in a list format (similar to CubeRankMember function) vs a delimited format? Thanks
Comment by: Jan Karel Pieterse (12-8-2014 11:07:16) deeplink to this comment
Hi David, Try this modified UDF:
Comment by: Kin (19-8-2014 23:11:37) deeplink to this comment
Just uncheck the slicer and update the pivot. Common Slicer connect to multi pivot table will encounter this error
Comment by: Ken (22-8-2014 16:49:16) deeplink to this comment
In my new dashboard I creating , I have 5 charts all with individual tab Pivots and 1 tab with all the data. I build Cell site What I'm trying to accomplish is to show which Cell Sites are completed (MS108) yet pending Transport (MS149/150). I select ALL of MS108 fields and only select the PENDING on the MS149 which pulls out all site pending Transport. The Remaining Milestones(MS), End to End - Fiber to MPOE; Room Ready, and Site walk and select on those that are pending. Each Milestone has it's own Pie Chart. so when I click on a certain Market; I.e. Ohip/WPA all charts work in synch and change they're data labels. I have two issues: 1)All the data Labels are the same when I click on a Slicer (could this be because I have all the Pivot connection selected ? 2) Each time I select another slicer, my individual Pivot change and it's difficult to get back to square one where nothing is selected.
Comment by: Jack Astill (22-8-2014 17:31:41) deeplink to this comment
Hi I have been trying to replicate this code for a project of my own for a few days on and off. I've found it really helpful, but if you follow the guide step by step it doesn't mention this part which is in the full script: "'Prevent event looping, changing a slicer in this routine also triggers this routine If mbNoEvent Then Exit Sub mbNoEvent = True bUpdate = Application.ScreenUpdating Application.ScreenUpdating = False" I am a relative novice for VBA so it didn't occur to me that this was the reason my code was falling on it's face every time. It might just be worth pointing out!
Comment by: Shanker (20-10-2014 13:49:37) deeplink to this comment
Hi - Thanks for such a wonderful function, it works perfectly fine with ordinary pivots and slicers, however, when i use PowerPivots, it throws application defined or object defined error on "For Each oSi In oSc.SlicerItems", I am not sure, why it works fine with ordinary pivots and when i am using powerpivot in excel 2103, it fails. Would appreciate any thoughts/help, you may have. thanks in Advance, Shanker
Comment by: Jan Karel Pieterse (20-10-2014 14:11:34) deeplink to this comment
Hi Shanker, That is why I show how to do this with a Powerpivot model using the CUBERANKEDMEMBER function.
Comment by: Isaac (5-11-2014 21:44:56) deeplink to this comment
I'm using Excel 2010 and I've created a dashboard using a pivot table and slicers. Is it possible to create a slicer that has like drop box characteristics that would enable to have 1 slicer but be able to see all of the data in that one header?
Comment by: Jan Karel Pieterse (10-11-2014 11:34:24) deeplink to this comment
Hi Isaac, I'm afraid you cannot change the way Slicers work.
Comment by: Marian (14-11-2014 23:06:26) deeplink to this comment
Stranded slicer caches? To summarize the long story below: Did I strand some old slicer caches that I should somehow have removed when I changed pivot caches with VBA? If so, how do I remove them? Is there a better way to do what I need to do? I have a workbook with multiple pivot tables and slicers. The workbook pulls data from MS CRM on line, using a spreadsheet that connects to CRM via FetchXml. That means that my pivot source is 'Sheet X'!Query_from_Microsoft_CRM. The query changes every month or two, although the columns returned always have the same names. I cannot edit the query from Excel--I have to make a new FetchXML query in CRM, pull the results into an Excel sheet as a dynamic table, attach the new query sheet to my workbook, disconnect slicers that are connected to multiple pivots, change the pivot data sources, and reconnect the slicers. I know just enough VBA to be dangerous, and am trying to automate the above. I've copied and use macros from this site and Contextures for listing pivots,pivot source data, pivot cache indexes, slicer caches, and pivot tables connected to slicer caches. Today, I disconnected the slicers, changed one pivot cache manually, noted the new cache index, and used approximately the following to change the pivot caches
Comment by: Jan Karel Pieterse (17-11-2014 09:38:00) deeplink to this comment
Hi Marian, In your specific case I'd be inclined to reconsider the design. I would suggest to use a separate workbook which pulls the data from the CRM system. Then in your report workbook simply use a static table as a source for the pivottables. If new data is needed, you empty that static table and paste the new data from the connected workbook into the report workbook. Doing that avoids having to do all the reconnecting of slicers and PT's.
Comment by: Brad (18-11-2014 13:14:15) deeplink to this comment
Hey, I am looking for a macro to be able to scroll through my slicer options (all of them ~ 50 options) and print each of the player reports that are associated with it. I have looked everywhere for one like this but neither understand it properly or can't find one that works with the slicer.. If anyone could help me out with a code that would be AMAZING and save so much time!! Thankyou
Comment by: Jan Karel Pieterse (18-11-2014 15:10:50) deeplink to this comment
Hi Marian, You could use code like this:
Comment by: apandit (24-11-2014 14:34:23) deeplink to this comment
HEllo I am trying to implement the above code - however some of the pivot tabels do not have all the field Pivot table 1. Global, Regional, Sub_Regional, Country Pivot table 2. has the above + Class_code Pivot table 3. has Region, Sub_Region and Country I get random results - in your article it says "If your situation differs (values in slicercache 1 might be missing in slicercache 2 or the other way around), you will need to take special precautions" Pls advice what is the best way to sync all these slicers. AP
Comment by: Jan Karel Pieterse (24-11-2014 17:06:37) deeplink to this comment
Hi Apandit, Well, suppose you selected region "France" in the slicer of PT1 but France is missing from the data for PT2. It is up to you to decide how you should now "filter" PT2. One way might be filter PT2 so it shows no data: Loop through all items of the associated Region field in PT2 and uncheck them so as not to show any data.
Comment by: Lars Larson (29-12-2014 17:51:59) deeplink to this comment
I adjusted the code you included to fit my purposes, of course...thanks. But I am seeing it throw an error on the line that I have as: oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True (1004:Application-defined or object-defined error) It is acting as thought the object have not been instantiated even though it makes it that far. It makes me think that the version of Excel (VBA) I am using (the latest version) is somehow working differently. that maybe the object model is an older one in this code. Anyone have any ideas for me?
Comment by: Jan Karel Pieterse (30-12-2014 10:37:24) deeplink to this comment
Hi Lars, Perhaps the last sliceritem cannot be selected because it is dimmed (due to another filter)?
Comment by: ravi asrani (16-1-2015 15:11:20) deeplink to this comment
1. Added slicer 2. when I select one Criteria in slicer I get data in pivot according to that criteria. 3. But when we double click on the figure for that criteria it gives full detailed data from that actual sheet instead of the only showing that data as per the criteria.
Comment by: Jan Karel Pieterse (17-1-2015 19:29:11) deeplink to this comment
Hi Ravi, Odd, as that normally also does not happen without a slicer. A slicer is just an additional way to operate the pivottable filters, the pivottables functionality stays the same.
Comment by: Viv (20-1-2015 13:56:13) deeplink to this comment
Hi, I have a pivot with a country field which has values such as 1. AU 2. US 3. AU; IN; TH; JP and so on. Is it possible to have a slicer or data validation list where the user can just type 'AU' and the pivot automatically selects all those records which have 'AU' in the country field? Thanks in advance.
Comment by: Jan Karel Pieterse (20-1-2015 15:00:11) deeplink to this comment
Hi Viv, Unfortunately you can only do that using the "normal" pivotfield filter dropdowns.
Comment by: Viv (21-1-2015 07:33:32) deeplink to this comment
Thanks Jan, for your reply. If that's the case where it can be done only by the "normal" pivotfield filter dropdowns, is there a way to capture what was typed? For example, if the user types "AU" in cell B1 in the filter drop down, it shows as "(Multiple Items)", but can I then have "AU" populated in cell C1, based on what was entered?
Comment by: Jan Karel Pieterse (21-1-2015 11:13:11) deeplink to this comment
Hi Viv, No, I'm afraid that is not possible. You could mimick the behavior using VBA and a dropdown box from the developer tab perhaps.
Comment by: Joe Van Nel (3-2-2015 10:06:22) deeplink to this comment
Hi Jan I have a workbook where I have a slicer connected to a small table (the table just shows the Last 2 months of a period)- eg: Dec and Jan The problem I have is that when the month changes and the pivot table updates to Jan and Feb - the slicer still keeps "Dec" even though it is not in the table anymore How can I prevent this from happening ? Thanks Joe
Comment by: Joe Van Nel (3-2-2015 10:17:16) deeplink to this comment
Hi Jan I have found the solution to my question on the slicer showing old data. Please ignore my previous question. In the slicer settings, you can untick the box that says "shows items deleted from data source" Thanks Joe
Comment by: Jan Karel Pieterse (3-2-2015 11:33:48) deeplink to this comment
Hi Joe, Good to hear you found your solution.
Comment by: Jeffrey (11-2-2015 15:56:12) deeplink to this comment
Just wanted to say thanks...your code was exactly what I needed to retrieve the slicer item selected by the user and then apply that to a heading filter. I can do this naturally in Excel 2013, but my company is still using Excel 2010.
Comment by: John (18-2-2015 00:01:20) deeplink to this comment
I'm having a bit of an issue with my slicers and was hoping to find a solution. I am trying to create a dashboard so that certain responsible persons can verify and confirm a payroll. Our policies are such that pay can be very different from one check to the next for any number of codes. I have pulled in two pivot tables based on a report that looks at all of the different earning codes on the check run that needs to be confirmed, and the previous check run, which has already been paid. One pivot table summarizes the data by earning code and each check run is a separate column. For example, Regular earning for 2/4 is $350k and for 2/18 it is $345k. Training pay for 2/4 is zero and for 2/18 it is $5k. The other pivot table summarizes the data by person and again each check run is a separate column. The difference on this, is that I have the data to display the difference from the previous check run. So for Rick, the total pay for 2/4 is blank (because it is the basis for comparison) and for 2/18 it is $1000 because he earned $1000 more on 2/18. I have a slicer for earning codes and it links to both pivot tables. If I click on the training pay code in the slicer, the pivot tables act in the following fashion. The first pivot table, the one that summarizes codes, hides the 2/4 column and only shows $5k under the 2/18 column. The second pivot table hides the 2/4 column and for the 2/18 column goes blank. It no longer shows the difference from the previous pay date. Is this because it is hidden so the 2/18 now becomes the basis for comparison? If so, is there a way to force columns on pivot tables to remain visible even if there is no data? It's nice that the second table only shows the employees who have training pay, but since it doesn't show any amounts next to them, it's not as useful as it could be. Any help would be greatly appreciated!
Comment by: Jan Karel Pieterse (18-2-2015 06:49:16) deeplink to this comment
Hi John, I expect the problem is not caused by the slicers, but rather by the setup of the pivottables. If you have fields that are calculated based off of the previous value, filtering affects the results. I would need to see your workbook in order to give a more useful advice though.
Comment by: Xolani (20-2-2015 08:43:54) deeplink to this comment
Hi guys. Can anyone help me here.. I have two pivotTables from different sources, however I would like to control both of them using only one slicer. Is there anyone who could help with the VBA code that connects two slicers
Comment by: Jan Karel Pieterse (21-2-2015 16:19:59) deeplink to this comment
Hi Xolani, There are some examples on this page: https://jkp-ads.com/Articles/slicers04.asp
Comment by: Mary (6-3-2015 17:21:58) deeplink to this comment
I have a Slicer that I want to be able to put them in the correct order. The only option I see is Alpha. My segments are U50, 50-99,100-499,500-999,1000-2999, 3000+. When I put them in a slicer the order is wrong. How do I fix that?
Comment by: Jan Karel Pieterse (6-3-2015 19:40:14) deeplink to this comment
Hi Mary, You should be able to add a custom list to Excel containing the needed order and then the slicer should sort using that list. Select File, Options, Advanced. Scroll down to bottom of screen and click "Edit custom lists". Click in the box next to "Import" and select the range of cells which have the list in the order you need.
Comment by: Samm (25-3-2015 21:52:53) deeplink to this comment
Is there a way of dynamically ordering the data in the slicer so that it always displays the most populous to least populous in the data table?
Comment by: Jan Karel Pieterse (26-3-2015 09:43:00) deeplink to this comment
Hi Samm, I don't think you can do that I'm afraid.
Comment by: Maria (31-3-2015 13:03:16) deeplink to this comment
How would you modify the vba to display only available items? For example: Let's say you have 2 slicers, one CITIES and one SHIP COUNTRY. If you select "Austria" in SHIP COUNTRY, only a select number of CITIES are now valid as selection in the CITIES slicer. How do I list those available items? Thank you. Maria
Comment by: Maria (31-3-2015 13:17:48) deeplink to this comment
Never mind. I figured it out. Thank you for your code and your time. Maria
Comment by: Jan Karel Pieterse (31-3-2015 14:46:45) deeplink to this comment
Hi Maria, It is always nice when people solve their own problems!
Comment by: CL (1-4-2015 16:00:14) deeplink to this comment
Hi, Does anyone have this working in a file they can share please? I am having issues getting the code to work. I have multiple external data sources and multiple slicers, so this looks like a very good solution. Any help much appreciated. Thanks, -CL
Comment by: Jan Karel Pieterse (2-4-2015 07:27:52) deeplink to this comment
Hi Rob, Drop me a line and I'll send what I have.
Comment by: Sunil (13-4-2015 12:21:01) deeplink to this comment
Hi Samm, I have a question around the number formatting of the entries that show up in the slicer window. The column (field) that I wish to add to the slicer is a currency column with 2 decimal places. For e.g. $1.00, $1.05, etc. which indicate the exchange rates. When I add this column to a slicer, the entries in the slicer window (where you choose from) show up as 1, 1.05, 1.1, etc. i.e. the formatting reverts back to general. Would you know how to fix this?
Comment by: Simon (21-4-2015 10:00:04) deeplink to this comment
Maria asked: > Let's say you have 2 slicers, one CITIES and one SHIP COUNTRY. > > If you select "Austria" in SHIP COUNTRY, only a select number of CITIES are now valid as selection in the CITIES slicer. Then replied that it had been solved. @Maria/JKP: How did you solve it? I have a similar requirement: 1) display in a comma-delimited list all slicer items that are still valid (not grayed out) after any filter is changed. 2) if all items are still valid it should show the entire list. 3) if no filter is active it should default to "no active filters" or similar insstead of listing every item in that slicer. Example: Slicer 1 'Flavor': Apple, Banana, Cherry Slicer 2 'Grocery': Yogurt, Candy, Soda Slicer 3 'Brands': ABCfood, BestCo, CoolSnacks Slicer 1 ('Flavor') has value "Apple" selected, which is captured by the "selected items" method on this page. All items in Slicer 2 are still in scope so the results list cell for 'Grocery' category should display "Yogurt, Candy, Soda". Only one item in Slicer 3 is still in scope so that cell should display 'BestCo', like this:
Comment by: Tom (27-5-2015 21:09:45) deeplink to this comment
This question is directly related, how can I edit my slicers once they are completely filtered?
Comment by: Jan Karel Pieterse (28-5-2015 09:24:28) deeplink to this comment
Hi Tom, I'm not sure what you mean by editing the slicer?
Comment by: Pooja Sivadas (5-6-2015 09:43:50) deeplink to this comment
The UDF code is just amazing. I have been searching for something like this since a long time. Thank you soo much.
Comment by: Christine Rufkahr (12-6-2015 23:26:25) deeplink to this comment
How can I make a Slicer Style that I have created available for all spreadsheets or at least for all future spreadsheets?
Comment by: Jan Karel Pieterse (13-6-2015 19:14:36) deeplink to this comment
Hi Christine, Create a workbook with a custom slicerstyle. When done and happy with it, copy a slicer with tht style applied to a new empty workbook. Delete the sycer, the style should stay in the workbook. Save-as the workbook as book.xltx to your XLSTART folder.
Comment by: Pooja Sivadas (16-6-2015 07:17:54) deeplink to this comment
Hi, there is one problem I'm facing when I use the UDF code. I pasted the code in one of my modules and it works fine.I have a few other Sub routines in my other modules. When I run some of them, on reaching an autofilter statement they are getting automatically redirected to this function but I'm not able to figure why this is happening. Can you please help me regarding the same. Why will a sub get redirected to a function without being called? Regards, Pooja
Comment by: Jan Karel Pieterse (16-6-2015 17:15:28) deeplink to this comment
Hi Pooja, This is because when you cause a recalc during debugging code, Excel's calculation engine will make calls to the User Defined functions and you will be stepping through them each time the function is called. You can get through the UDF calls in one keystroke during debugging by hitting control+shift+F8.
Comment by: Pooja Sivadas (18-6-2015 06:55:49) deeplink to this comment
That's great Jan. It works. Thanks. Does that mean the redirecting happens only when we run the code line by line (F8)? I'm worried that the redirection is slowing down my other subs when I run them at once (using F5) as well.
Comment by: Jan Karel Pieterse (18-6-2015 10:31:50) deeplink to this comment
Hi Pooja, No it just means that whenever your code triggers a calculation that hits cells with your UDF, the UDF gets called for those cells.
Comment by: Pooja Sivadas (18-6-2015 11:01:57) deeplink to this comment
Oh! So that means it will slow down my subs right? Is there any way I can prevent this? (Can't use Application.Calculation=xlCalculationManual as my code depends on some of the calculated fields from the worksheet)
Comment by: Jan Karel Pieterse (18-6-2015 16:02:37) deeplink to this comment
Hi Pooja, You could add a switch to your UDF that turns it off, but that will probably cause their calling cells to get wrong results as well. You could control whether an individual worksheet is allowed to recalc perhaps?
Comment by: FLORE (22-6-2015 22:38:12) deeplink to this comment
I never use VBA but your code here is exactly what I need. However, I am so new that I don t know where to put the code (my slicers are copied into a worksheet but the pivot tables are in another one (this code should be where the slicers are true?) then my other question is how can I put only one selection field for eac slicers that makes your code run? in my example I have 10 to 15 pivot tables. These pivot tables are based on 3 data sources, meaning that for the same selection field (I have 3 Country, Station, Months) I have 3 slicers for each filter...) the idea to make it friendly for other user is to have only one slicer for month, one for country and one for station that allows to synchronise the 9 slicers I had to create. I hope it is not to confused and you can help me.
Comment by: Jan Karel Pieterse (23-6-2015 07:36:37) deeplink to this comment
Hi Flore, The code shown all goes into the ThisWorkbook module of your Excel workbook.
Comment by: Flore (23-6-2015 13:58:25) deeplink to this comment
Dear Jan, Thanks for your answer. I still have 3 questions: 1- in your code the only part I don t understand is the following Dim sYear As String . I don t follow why it has been added, is it because the Year (in your code) is the only slicer linking all the pivottables (same filter in each one)? In this case, if I have 3 slicers filtering in each pivottables (some linked with the same datasource and some with others), shall I add the same Dim for each slicers caption name? 2- IS your code valid for slicers that have the same caption name but each slicer comes a different a different data source example Months from datasource 1, Month from data source 2 and Month from data source 3? 3- If the answer is yes, if I only click on one slicer, all the slicers having the same name will synchronise (eg. Month one)? Many thanks again for your help
Comment by: Flore (23-6-2015 15:54:24) deeplink to this comment
Your code is just amazing and works (please forget my previous questions except the one). I have a difference in the setting because one of my slicers caché referring to station is having a slicercaché of station in one slicer and origin_city in the other 2. moreover the list of station/city is different as a result the same station as the first one is selected but another 2 are also selected in the longer list of cities. How can I send you the code to see how I can fix it? Thanks for your help :)
Comment by: Jan Karel Pieterse (23-6-2015 17:24:50) deeplink to this comment
Hi Flore, 1: sYear is not used so the declaration can be removed :-) 2: The code is ONLY needed for slicers which are using different pivotcaches, if they share the pivot cache, tie them together using the Pivottable connections of the slicers. 3: they should :-)
Comment by: Flore (24-6-2015 22:26:24) deeplink to this comment
Sorry, me again... Your code is working with one of th eslicers but not with the other 2. I have 3 slicers per each datasource associated to several pivot tables. Each slicer have the same caption name for each data source -Months -Station -Country For the month slicer the name "Months" is in allat the same place For the station one, the slicer caché are the following: Slicer_Pos_Station_Code / Slicer_Origin_City_Code and Slicer_Origin_City_Code1 For the country: Slicer_Pos_Country_label / Slicer_Origin_Country_Code and Slicer_Origin_Country_Code1. I think that it is where I have an issue and the code is not working. I have tried to manage it in the Mid function and also adding a "or" function to include the City name. Can you help me adapting your code? many thanks in advance
Comment by: Jan Karel Pieterse (25-6-2015 08:14:01) deeplink to this comment
Hi Flore, If you email your workbook I will try to help. However: No promises, as I'm really busy.
Comment by: Alliesong (6-8-2015 10:17:36) deeplink to this comment
How to get slicers to stay in one place on the sheet and not move when filters are selected and the pivot table below changes size,eg, has less columns
Comment by: Jan Karel Pieterse (6-8-2015 11:02:42) deeplink to this comment
Hi Allie, I would expect changing the property so the slicer does not move, nor sizes with the cells should do the trick?
Comment by: Stuart Dunlap (6-8-2015 20:56:48) deeplink to this comment
Hi Jan, Thanks for this terrific summary! I'm hoping you can give some insight on how to improve performance for a macro I have. I have six slicers attached to five PivotTables in an Excel 2010 workbook. The data source for the PivotTables is a SSAS OLAP cube. I'm using the following code to update the Pivot Slicer parameters:
Comment by: Jan Karel Pieterse (7-8-2015 14:17:00) deeplink to this comment
Hi Stuart, Hmm, I would not expect that changing a slicer would trigger a query update to begin with. Is the pivottable in question set to not save data with it? Does a manual slicer selection trigger the query refresh as well?
Comment by: Stuart Dunlap (7-8-2015 14:52:03) deeplink to this comment
Jan - thank you for the post. To answer your first question ... this PivotTable is connected to a SSAS OLAP cube, so I don't have the option of saving the data with it. Under the Data tab of the PivotTable Options dialog, the button 'Save source data with file' is grayed out. To answer your second question, I did a manual slicer selection change and that did trigger a query refresh. In fact, it generated multiple query refreshes - bonus! :-) (I'm saying it triggered query refreshes because the lower right section of the status bar said 'Running OLAP query ... (Press Esc to Cancel)' and this message flashed five times, I'm guessing one flash for each PivotTable.) I did some research after my post yesterday and I happened across the following site that would seem to suggest there is no way to control the query refresh in Excel 2010: https://social.msdn.microsoft.com/Forums/office/en-US/4f28c068-150b-4aa1-bf04-3fdb2bac4b1a/olap-cube-pivot-table-update-refresh-on-slicer-selection-changes?forum=exceldev However, after doing the manual update to answer your second question, I'm wondering if there would be a way through VBA to 'Hit the Escape' key to prevent the refresh, and then do one last refresh after I've updated all my Slicers. Do you know of a way to 'Hit the Escape key' with VBA?
Comment by: Jan Karel Pieterse (10-8-2015 10:47:04) deeplink to this comment
Hi Stuart, Well, you can send a key to Excel:
Comment by: Flore (25-8-2015 15:57:31) deeplink to this comment
Dear Jan, Sorry to bother again, I fix the issue I posted on the 25th of June by creating the same field names for each of the data sources. Your macro works very well and I thank you for that. But there is always a but (I am still not good enough to make big changes by myself) recalculation takes 2 long if I base this macro on the whole work book (the data sources sum more than 350 MB) so my idea is to split this macro in one for the 1st worksheet that needs the synchronization and in another one for the other. The first work sheet name is equ and the other one is equ HEA how shall I write this in the macro instead of "Thisworkbook"? I suppose doing this the synchronization will take place only for the worksheet I am working on true? Thanks again for your help
Comment by: Sabrina (25-8-2015 20:13:52) deeplink to this comment
I am attempting to update (sort by columns) an Excel table based on a slicer. While the vba code will work; it will only update after I make a selection if I run the actual macro. However, I need for the table to update every time I make a selection from the slicer. What am I missing? Sub UpdateTSM() Dim sc As SlicerCache Dim si As SlicerItem Dim i As Integer Dim oneRange As Range Dim aCell As Range Set sc = ActiveWorkbook.SlicerCaches("Slicer_market") Set oneRange = Range("Table6") Set aCell = Range("I10") For Each si In sc.SlicerItems If si.Selected = True Then oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlYes End If Next si End Sub
Comment by: Jan Karel Pieterse (26-8-2015 11:24:57) deeplink to this comment
Hi Sabrina, Unfortunately, Table slicers trigger no specific event like slicers on Pivottables have (the Workbook_SheetPivotTableUpdate in the ThisWorkbook module). So all you can do is use the Change event of the worksheet in question and add some logic that detects whether the changed cell is within the table the slicer is driving. From that event you can call your current macro.
Comment by: Jan Karel Pieterse (26-8-2015 11:34:00) deeplink to this comment
Hi Flore, It depends. A Slicer can be placed on ANY worksheet, not necessarily on the same sheet that the pivottable the slicer controls is on. Also, one slicer can control multiple pivottables on multiple worksheets. So what the code needs to do precisely, depends on your workbook setup and your requirements.
Comment by: SJ (8-9-2015 20:06:56) deeplink to this comment
Hi Jan, This is really great. It works perfectly for my purposes. However, I was wondering if this can be done with timeline slicers. For regular slicers, multiple slicers get synched, but for timeline slicers, i noticed that this code doesn't work. Is there a separate name for SlicerCache for Timeline Slicers? Or is this even possible? Thanks Sean
Comment by: Sean (8-9-2015 20:09:38) deeplink to this comment
Hi Jan, Thanks for this wonderful post. I was able to adapt this code for my purposes. I just have one follow up question. Can you do this for Timeline Slicer? I was able to do it for regular slicers, but not Timeline Slicer, with all the parameters you described consistent. Once again, I appreciate this post. Thanks!
Comment by: Arthur (15-9-2015 21:38:14) deeplink to this comment
This looks really neat and almost got it to work. Can you send an example excel file with the synchronizing slicers?
Comment by: Rakesh (29-9-2015 20:09:27) deeplink to this comment
Is there a known problem using slicers for pivot tables with calculated items? Quick google search didn't reveal anything. I have 3 slicers. Usually if I choose a value from the 1st slicer; the 2nd and 3rd change such that items with no data are shaded out (per the rules of the style). This was working but as soon as I added a calculated field it believes that there is always data and hence it is not greyed out.
Comment by: Jan Karel Pieterse (30-9-2015 11:46:14) deeplink to this comment
Hi Rakesh, That is new to me, I never heard of that problem. That does not mean the problem doesn't exist though :-)
Comment by: Tanya (1-10-2015 17:45:57) deeplink to this comment
If trying to create a slicer that will expand and reduce columns through a horizontal filter... is that possible? Slicers usually function as vertical filters, but what if I want a slicer that will specifically select the headers that will be in my pivot? Essentially this would be a horizontal filter on the column headings. Is that possible without using power pivots?
Comment by: Jan Karel Pieterse (2-10-2015 15:00:23) deeplink to this comment
Hi Tanya, Yes, that should work. You can add any field of your source data as a slicer.
Comment by: Gerald Vaughan (30-10-2015 15:14:14) deeplink to this comment
Is it possible to edit slicer names to accommodate using a prior spreadsheet for a new project?
Comment by: Jan Karel Pieterse (30-10-2015 16:29:33) deeplink to this comment
Hi Gerald, I don't think you can edit the name to use in formulas. You can edit a slicers name in the slicer settings dialog however.
Comment by: Joe G (10-12-2015 06:46:59) deeplink to this comment
Any way in 2013 to have no fill color surrounding the slicer button? Deleting th box lines works, but I find no way to delete the fill color.
Comment by: GuyC (10-12-2015 15:21:02) deeplink to this comment
Your blog has helped HUGELY to get me to a point where I can interrogate SlicerItems and iterate through them. I want to cycle through the slicer items, select one item (1=True, others = False), perform an action, then move on to the next. I have the code below, which works nicely (I've had Debug.Print's in there to show values etc as it run, and I can see the SlicerItem values are found and being compared in the IF/THEN). However... I get the old error "Run-time error 1004: Application-defined or object-defined error" when it reaches line>> oSi.Selected = True Any ideas? How do I set the Selected value (True/False) for the active SlicerItem (oSi or oSi1)?
Comment by: Jan Karel Pieterse (10-12-2015 22:56:59) deeplink to this comment
Hi Guy, Nothing appears to be wrong with your code, perhaps something I am overlooking. Can you email the file to me perhaps?
Comment by: Jan Karel Pieterse (10-12-2015 23:02:05) deeplink to this comment
Hi Joe, I don't get the point I'm afraid, how will you be able to see what the slicer is filtered on without the fill color?
Comment by: Joe G (10-12-2015 23:54:41) deeplink to this comment
Re my previous, I want to remove the fill of the box thatsurrounding the slicers. I am using a photographic background for the page and want just the slicer buttons to appear (no visible box (fill) around them. How do I get a transparent option?
Comment by: Jan Karel Pieterse (11-12-2015 12:03:17) deeplink to this comment
Hi Joe, Aha, now I understand. I'm sorry to say you cannot set a slicers background to transparent.
Comment by: Dmitry (26-12-2015 21:51:45) deeplink to this comment
Hi, Many thanks for the effort in writing this code. It perfectly suits my case, but doesn't SOLVE it. I tried 5-6 macros (went deeply into Googling) that would solve my case, but non of them could synchronize / mimic / mirror the selected items on the Slicer. Just a few facts: - 1 data-set responsible for 3 pivots (and 2 pivot charts from those), another data-set responsible for another pivot -1 data-set contains weekly values, second data-set - monthly - both contain IDENTICAL parameters as Forecast Period, Country, Location, and Product - slicercaches names are for example: Slicer_Forecast_Period, and Slicer_Forecast_Period1 Assuming that everything is the SAME I should be able to have only 1 slicer instead of 2. Finally, the code provided by you was adjusted to suit my case. And I'm not new to VBA, got some basic experience. Why it doesn't work? Any ideas...? Cheers, Dmitry
Comment by: Dmitry (27-12-2015 13:22:23) deeplink to this comment
Goedaag Jan Karel, Please ignore my previous message / comment. I made some progress and macro is now triggered. However, I'm getting an error "Method 'Selected' of object 'SlicerItem' failed" on the line oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True 'error here For Each oSi In oScForecast.SlicerItems
Comment by: Jan Karel Pieterse (28-12-2015 15:59:07) deeplink to this comment
Hi Dmitry, For slicers tied to the datamodel (Excel 2013 and up), the Selected property does not work. I'll have to look if I can find a working example.
Comment by: Dmitry (28-12-2015 16:11:53) deeplink to this comment
Dear Jan, I'm using Excel 2011, not 2013. :) concerning the "Selected" property, I've tried usinng
Comment by: Joe G (29-12-2015 02:48:56) deeplink to this comment
Comment by: Jan Karel Pieterse (29-12-2015 11:52:46) deeplink to this comment
Hi Dmitry, Perhaps you can use the VisibleSlicerItemsList property:
Comment by: Jan Karel Pieterse (29-12-2015 11:53:16) deeplink to this comment
Hi Joe, This cannot be done with slicers.
Comment by: sathish Kumar (5-1-2016 13:30:25) deeplink to this comment
Comment by: Jan Karel Pieterse (6-1-2016 16:10:03) deeplink to this comment
Hi sathish, What error do you get precisely?
Comment by: Hans Henriksen (9-1-2016 22:30:33) deeplink to this comment
Great code, I was searching for this for days, but the UDF does not work with XP SP3 and Excel 2010, it gives runtime error 1004. However, I got it to work by adding an extra object:
Comment by: Alejandro Alarcon (11-1-2016 08:03:58) deeplink to this comment
i need some help plz! I'm trying to do a macro that create a slicer for ANY table not only one in specific but it only works for a specific table because the macro reads the table's name and use it through the whole macro. this is the maro and you can see that it says "tabla2463", is there any way to generalize this macro so it works for any table? Sub test() ' ' Macro create a slicer for a table ' Range("A2").Select ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.ListObjects("Tabla2463"), _ "COMM PERCENTAGE").Slicers.Add ActiveSheet, , "COMM PERCENTAGE", _ "COMM PERCENTAGE", 129, 519.75, 144, 198.75 ActiveSheet.Shapes.Range(Array("COMM PERCENTAGE")).Select With ActiveWorkbook.SlicerCaches("SegmentaciónDeDatos_COMM_PERCENTAGE") .SlicerItems("6").Selected = True .SlicerItems("1.5").Selected = False End With
Comment by: Jan Karel Pieterse (11-1-2016 13:18:46) deeplink to this comment
Hi Alejandro, You could replace this: ActiveSheet.ListObjects("Tabla2463") with ActiveCell.ListObject
Comment by: Brandon (11-1-2016 16:20:12) deeplink to this comment
Hi Jan, Also wanted to say fantastic solution to this problem. However, is it possible to get this to work with multiple slicers? I have four slicers and I'd like each one to have its selected slicers appear in a different cell. I've managed to have all four slicers show their selected items in a single cell but this looks cluttered and awkward.
Comment by: Jan Karel Pieterse (11-1-2016 17:28:23) deeplink to this comment
Hi Brandon, Here is an alternative version which has an extra argument with which you can choose to either get all items in a single cell, or return a list of them. For the latter to work you must array-enter the formula into as many cells as you expect to have sliceritems.
Comment by: Brandon (12-1-2016 19:56:01) deeplink to this comment
Hi Jan, Thanks for the amazingly fast reply! One thing I didn't realize in your original code is that SlicerName is not supposed to be replaced (I put the name of one of my slicers in there). Sussed that out and everything works great with your modification. Thank you so much!
Comment by: Jan Karel Pieterse (13-1-2016 08:26:01) deeplink to this comment
Hi Brandon, You're welcome!
Comment by: Bjørnar K. (14-1-2016 14:11:50) deeplink to this comment
Hi there, Is it possible to have settings that excludes the slicer when printing out the excel sheet? best regards Bjørnar
Comment by: Jan Karel Pieterse (14-1-2016 14:34:13) deeplink to this comment
Hi Bjørnar, Sure, right-click the slicer and select Size and Properties. Click the Properties tab and uncheck the box.
Comment by: Amber J (14-1-2016 20:28:43) deeplink to this comment
Hi Jan, Do you know of a way to sync a timeline slicer with another slicer. I have a pivot table that has dates and locations. Not all locations are used in each month range which is why I was wondering if it is possible for only the locations valid for the selected month will appear in the slicer. Thank you, Amber J
Comment by: Jan Karel Pieterse (15-1-2016 11:40:42) deeplink to this comment
Hi Amber, Slicers are supposed to handle that situation out of the box already, no need for any programming.
Comment by: Eugene (29-1-2016 10:04:05) deeplink to this comment
Hi Jan Karel Pieterse I need to select first slicer item that currently have data. But my pivot table is getting data from an external source. Could you please share an example how to do that? Beacause my code is same as GuyC and I got same error(1004). Best regards, Eugene
Comment by: Jan Karel Pieterse (29-1-2016 14:28:55) deeplink to this comment
Hi Eugene, It is probably something like this (not tested):
Comment by: Diogo (31-1-2016 14:05:17) deeplink to this comment
Hi Jan, Thanks so much for your explanation. It is been very exciting to learn all about slicers. I have a problem that should be simple. I have 4 slicers connected to the same pivot table and would like to pass on to GetSelectedSlicerItems only the slicer that actually had an item selected. How can i do this? Is there any how to find out which of the slicers triggered the Workbook_SheetPivotTableUpdate?
Comment by: Jan Karel Pieterse (1-2-2016 09:57:13) deeplink to this comment
Hi Diogo, I'm afraid there is no simple way to detect which slicer was clicked. The only way I would know is to keep score of which filtes were applied and then try to derive the clicked slicer from the newly applied filters. Given that a click on one slicer may als affect what is displayed on another slicer, this is not easy at all.
Comment by: JHN_DK (5-2-2016 15:04:24) deeplink to this comment
Hi Jan, Very useful comments, thanks. I tried following of your macro's: Public Function GetSelectedSlicerItems... etc, and that works as expected. BUT my issue is that it only works if I do the selection in the slicer itself. Meaning that the =GetSelectedSlicerItems does not give the result when the sliceritems are reduced as impact from slections in other slicers. Any suggestions? Thanks in advance, JHN_DK
Comment by: Jan Karel Pieterse (5-2-2016 15:49:19) deeplink to this comment
Hi CHeck out this version, which has an additional parameter that you can set to true or false to either ignore or not ignore items that other slicers have filtered out:
Comment by: JHN_DK (8-2-2016 11:56:42) deeplink to this comment
Hi Jan, Thanks, just what I was looking for. I must say: super solution and super quick reply :-)
Comment by: Jan Karel Pieterse (8-2-2016 13:42:56) deeplink to this comment
Hi Joakim, You're welcome!
Comment by: JHN_DK (8-2-2016 15:58:39) deeplink to this comment
Jan, Do you also have a trick on how to load that list of "GetSelectedSlicerItems" into another slicer (to synzhronize two slicers with different data sources)? It's just a long shot as that would save me a lot of time. Thanks in advance, JHN_DK
Comment by: Jan Karel Pieterse (8-2-2016 16:08:41) deeplink to this comment
Hi Joakim, No, but you should be able to use the example code above to get this working?
Comment by: Laura (12-2-2016 21:02:13) deeplink to this comment
Hi Jan - I read through all the comments, appreciating the effort you put into helping people! However, I don't see this problem: I have slicers on a Pivot Chart (Power Pivot actually) in Excel 2010. I was able to format the width of buttons, number of columns, position on the sheet, etc on some of the slicers at some time, but not all of them. Today, two of the 3 slicers will not behave - I can't move them nor resize them - they keep autoformatting to an unacceptable appearance. Any thoughts? thanks a bunch
Comment by: Jan Karel Pieterse (13-2-2016 15:56:01) deeplink to this comment
Hi Laura, I can't say I recognize this. Perhaps it helps to simply get rid of them and add them back?
Comment by: Raja (22-2-2016 10:29:45) deeplink to this comment
when I update the Pivot table sourec by the below code it gets updated but the Slicer is not updating. Please hlep. ActiveWorkbook.Worksheets("SUMMARY").PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:="named range", _ Version:=xlPivotTableVersion10)
Comment by: Jan Karel Pieterse (22-2-2016 11:14:22) deeplink to this comment
Hi Raja, What happens precisely?
Comment by: Aura Reyes (29-2-2016 14:21:08) deeplink to this comment
Hi Jan, Thats exactly I was looking for.... but I found an issue. It Works perfectly in a pivot table of a local data base in Excel, but when I tried to do it in another pivot conected to an SQL Data Base through an ODBC conection in Power Pivot shows the follow message: "Not Ítem Selected" Can you help me please !!!! PD. Sorry for my bad english, :-)
Comment by: Jan Karel Pieterse (29-2-2016 18:03:21) deeplink to this comment
Hi Aura, SLicers tied to Powerpivot pivottables or to the Excel 2013/2016 datamodel behave different. I have made a modification to the function which now should work for both cases:
Comment by: Jacques Major (1-3-2016 20:40:15) deeplink to this comment
Hi, I've found the issue regarding the drilldown not being filtered when using a slicer. The field used in the slicer must either be a part of the pivot table or a filter on the pivot table for it to work. If it isn't in the pivot table either as a field or filter, the filter will not work and will show all of the data instead. I have 8 different slicers tied to several pivot table and having the filters showing on all the pivot tables just makes it confusing. Is there a way to work around this?
Comment by: Jan Karel Pieterse (1-3-2016 21:25:16) deeplink to this comment
Hi Jacques, I'm afraid there isn't much you can do other then e.g. hiding the rows with the page filters in question or by placing slicers on top of them.
Comment by: Jacques Major (1-3-2016 21:28:12) deeplink to this comment
Thanks for looking into Jan. That was the solution I went with. It's too bad Microsoft hasn't seen fit to tie the drill to the filters applied in the segment. It's a little confusing for users seeing a lot more data than shown in the pivot table when they drill down.
Comment by: Reuben Cofie (4-3-2016 16:58:21) deeplink to this comment
This is an excellent site. It has given answer to a problem I have been searching for a couple of days now. I need a help to create an ACCOUNTABILITY DASHBOARD for a health organization. This dasboard is intended to tack accountability of officers who have been given responsibilities to accomplish. The proposed indicators for the dashboard are as follows: ACTIVITY TYPE, PROBLEMS REPORTED, REPORTED BY, REPORTED TO, DECISIONS TAKEN, PERSON RESPONSIBLE FOR ACTION, TIMELINE, ACTION TAKEN/NOT. Please I would like to know if combos could be created for the slicers to select the options (if so how?) instead of the default which displays all the options on the slicers occupying space. Thanks and kind regards.
Comment by: Jan Karel Pieterse (4-3-2016 20:53:40) deeplink to this comment
Hi Reuben, It is a bit hard to advise without an idea about the data. Perhaps you'd better ask this question at http://www.eileenslounge.com as there you can upload example files and have more people available for help.
Comment by: Rakesh (7-3-2016 10:27:34) deeplink to this comment
I am looking for macro which can change the pivot table filed based on silcer selection. For example I have three silcer Options L1/L2/L3 and if I select any time in L1 Sicer cach then pivot table filed should changed and if I clear filter on L1 slicer caches then again it should bring back with original filed so bacially I want to show the info as per slicers selection Hope you understand my query Thanks in advance for your support
Comment by: Nicolas (8-3-2016 11:32:51) deeplink to this comment
Hello Jan, I think I'm encountering the same problem as T. (5/22/2012 2:30:49 PM)but having read the whole forum I couldn't find the answer. Let me present it my way. I use Excel 2010 Reports filtered by several slicers to then create graphics off a PivotTable located in Access 2010. When some figures in the graphics are unexpected, I'd like to go to the underlying report double click on these figures to get the pivot table entries the the number is based. Unfortunately, when I do that I get a new sheet with a list of entries that is much larger than what is synthesized by the number I was investigating. I'm tempted to say that the list on that sheet does not take into consideration the slicers. Would you by any chance have a way around that ? I ' be very thankful. Thank You for this great reference page
Comment by: kschaefer (10-3-2016 23:16:34) deeplink to this comment
is there anyway to reset the slicers to show all after manually changing the selection. I have tried ClearManualFilter and it seems to be buggy. K
Comment by: Jan Karel Pieterse (11-3-2016 10:30:18) deeplink to this comment
Hi Rakesh, I'm afraid I don't understand what you need, sorry! Can you try to rephrase the question? Alternatively, go to http://www.eileenslounge.com and ask your question there.
Comment by: Jan Karel Pieterse (11-3-2016 10:32:51) deeplink to this comment
Hi Nicolas, If a slicer filters a field that is NOT in the pivot table, the filter settings of that slicer are ignored when you drill down. The only way around that I know of is either by adding that field to the PT (as a page field perhaps) or by using a VBA event to filter the data after the drilldown.
Comment by: Nicolas (11-3-2016 11:28:56) deeplink to this comment
Thank you Jan ! Indeed, coupling the slicer with the report gives you the numbers you want BUT if you want the drill down to be coherent with that number you must take care to include in addition all "sliced" fields at least as a pages to the report you're "drilling". Works great ! Thanks again
Comment by: rene vis (22-3-2016 17:24:07) deeplink to this comment
Dag Jan Karel, net vandaag heb ik onder naam 'Rene' een post gedaan op StackOverflow over dit onderwerp gaat. Zie http://stackoverflow.com/questions/26810376/synchronizing-slicers/36157201#36157201 Ik heb een andere aanpak gekozen voor het probleem: "Approach is by saving state of all slicer cache objects. After a change in a pivot table new states can be compared with old states and a list of updated caches composed. Once this list is known, synchronizing can be accomplished." Met dank aan je voorbeeld, Rene Vis
Comment by: Jan Karel Pieterse (27-3-2016 20:20:13) deeplink to this comment
Hi Rene, Nice example. Though I wonder whether putting the selected items into collections might be more efficient than a concatenated string.
Comment by: Debbie (14-4-2016 00:02:21) deeplink to this comment
I have two slicers on my picot table for enrollment. One for Department and one for Course#. By nature of the sheer number of courses that we have the slicer is only showing a portion of them at a time. I have the slicer settings as "Show items with no data last". When a user scrolls down looking at a long list of courses for a particular department the scrollbar stays in that position. When they then select another department with say fewer course selection in the Course# slicer they only see the greyed out ones and have to manually move the scrollbar up to the top. Many users do not figure this out for some reason. I want the course slicer's scrollbar to move to the top anytime a selection is made in the department slicer. Debbie
Comment by: Jan Karel Pieterse (14-4-2016 10:11:19) deeplink to this comment
Hi Debbie, Perhaps you can use (part of) this code. It is a bit cluncky but it seems to work:
Comment by: Ivan (3-5-2016 22:53:25) deeplink to this comment
This is so freakin' helpful! This totally just saved me when no other solution seemed viable. Thanks!
Comment by: SCherian (5-5-2016 22:05:47) deeplink to this comment
I read your blog and many people posted the same question and I don't find a solution in any of the post. Here is my problem. I'm trying to select slicer items based on a value in drop down. I tried so many different ways but I'm getting "application defined or object defined error". Slicers on my sheet from multiple sources. So I'm using SlicerCacheLevels as you suggested in your blog. I'm able to get the values. But when I try to set sI.Selected = False/True I'm getting this error. Could you please tell what I might be doing wrong. I appreciate any help. <VB> Sub Step_Thru_SlicerItems(ByVal SlicerName As String) Dim sC As SlicerCache Dim sI As SlicerItem Set sC = ActiveWorkbook.SlicerCaches(SlicerName) Dim c As Range Dim Rng As Range Set Rng = Range("ActivityMonths") For Each c In Rng For Index = 1 To sCL.Count For Each sI In sC.SlicerCacheLevels(Index).SlicerItems 'On Error GoTo errHandler Debug.Print "Slicer Item : " & sI.Value & " Selected : "; sI.Selected sI.Selected = False Next sI Next Index Next c errHandler: Application.EnableEvents = True Exit Sub End Sub </VB>
Comment by: Jan Karel Pieterse (11-5-2016 15:55:09) deeplink to this comment
Hi SCherian, Sorry for the belated reply. Look for a post by me dated 1/29/2016 2:28:55 PM, it may contain just the code snippet you need.
Comment by: SCherian (11-5-2016 16:35:28) deeplink to this comment
Hi Jan, I have modified the code based on your 1/29/2016 2:28:55 PM post. But I'm getting the same error "application defined or object defined error" as mentioned in my previous post. Please help. <VB> Sub Step_Thru_SlicerItems(ByVal SlicerName As String) Dim sC As SlicerCache Dim sI As SlicerItem Dim sCL As SlicerCacheLevel Set sC = ActiveWorkbook.SlicerCaches(SlicerName Dim c As Range Dim Rng As Range Set Rng = Range("ActivityMonths") For Each sCL In sC.SlicerCacheLevels For Each sI In sCL.SlicerItems If sI.HasData Then Debug.Print "Slicer Item : " & sI.Value & " Selected : "; sI.Selected sI.Selected = False End If Next sI Next sCL End Sub </VB> Thanks, SCherian
Comment by: Jan Karel Pieterse (11-5-2016 17:33:05) deeplink to this comment
HI SCherian, The code you post does not resemble the one I refer to, especially the part that actiually does the selecting:
Comment by: SCherian (11-5-2016 18:41:17) deeplink to this comment
Hi Jan, I wanted to select multiple items in the slicer list based on a drop down value. Yes, with the code <vb>oSc.VisibleSlicerItemsList = Array("[TableName].[SlicerName].&[" & oSi.Value & "]") </vb> I was able to select only one item. Is there a way to select multiple items. Thanks, Scherian
Comment by: SCherian (12-5-2016 00:29:50) deeplink to this comment
Hi Jan, Thank you so much for the help. You shed light to solve my problem. In order to select multiple items in a slicer all I have to do is add those items that I have to multi-select into an array and assign that Array to the VisibleSlicerItemsList. I'm posting the code here that worked for here coz it may help someone with the same usecase. I'm passing slicer name and the name of the named range (the values that needs to be set in the slicer is stored in a namedrange cells. These values are coming from the database based on a selection criteria). The better approach in the logic would be, instead of looping through the slicer items, construct the array by looping through the values arraylist(i) = "[TableName].[SlicerName].&[" & value(i+1) & "]" and set the VisibleSlicerItemsList = arraylist. Here is the code snippet that worked for me: <VB> Sub UpdateSlicer(ByVal SlicerName As String, ByVal RangeName As String) Dim sC As SlicerCache Dim sI As SlicerItem Dim sCL As SlicerCacheLevel Set sC = ActiveWorkbook.SlicerCaches(SlicerName) Dim c As Range Dim Rng As Range Set Rng = Range(RangeName) Dim monthList() As Variant Dim Index As Integer Index = 0 ReDim monthList(0 To (Rng.count - 1)) On Error GoTo errHandler For i = 1 To sC.SlicerCacheLevels.count For Each sI In sC.SlicerCacheLevels(i).SlicerItems 'Debug.Print "Slicer Item Name :" & sI.Name For Each c In Rng If sI.Value = c Then monthList(Index) = sI.Name Index = Index + 1 End If Next Next Next i sC.VisibleSlicerItemsList = monthList errHandler: Application_On Exit Sub End Sub </VB>
Comment by: Jan Karel Pieterse (12-5-2016 07:40:26) deeplink to this comment
Hi SCherian, Yes that would've been my answer too. Good work finding the answer!
Comment by: Wolgrand (23-5-2016 00:33:01) deeplink to this comment
Hi guys. All wise tips. But Does any one have clue to work with 3 Slicers and the cell just return the last selected item ? Best Regards
Comment by: Jan Karel Pieterse (23-5-2016 09:27:15) deeplink to this comment
Hi Wolgrand, It shouldn't be very hard to get the last valid entry from the UDF formula result using normal worksheet functions. Perhaps: =INDEX(A1:A10,MAX(IF(ISERROR(A1:A10),0,ROW(A1:A10)))) A1:A10 is the precise area the UDF is array-entered into. (array entered using control+shift+enter)
Comment by: AK (26-5-2016 10:44:50) deeplink to this comment
Hi, I need to enter manual value in slicer. Need help to add this value manually. Thanks AJ
Comment by: Jan Karel Pieterse (30-5-2016 18:08:49) deeplink to this comment
Hi AK, I am not sure what you mean?
Comment by: Joe (12-6-2016 15:58:54) deeplink to this comment
i am using excel 2010- Microsoft professional plus, and when i click on the slicer , after selecting any cell from a table, it opens a dialog for existing connection . how can i solve this ?
Comment by: Jan Karel Pieterse (13-6-2016 17:40:08) deeplink to this comment
Hi Joe, Sounds like the table has an external connection which needs credentials. What happens if you refresh the table first?
Comment by: Anna (23-6-2016 19:07:26) deeplink to this comment
Hello, I trying to do macro for slicer in pivot table. If I choose any store name other rows should hide. Sub Store() Dim wb As Workbook Dim sc As SlicerCache Dim si As SlicerItem Application.ScreenUpdating = False Application.EnableEvents = False Set wb = ThisWorkbook Set sc = wb.SlicerCaches("Slicer_Store") Set si = Nothing On Error Resume Next For Each si In sc.BoxSlicerItems If si.Selected = True Then Rows("41:2500").EntireRow.Hidden = True Else Rows("41:2500").EntireRow.Hidden = False End If Next si End Sub What i am doing wrong? it's not choosing any stores, please help..
Comment by: Jan Karel Pieterse (24-6-2016 11:20:52) deeplink to this comment
Hi Anna, Not sure what you are trying to achieve. It seems there is no such property as "BoxSlicerItems", so your code wont work indeed. Apart from that, why hide rows, the slicer should be taking care of hiding stuff without any VBA code needed?
Comment by: Allison (27-6-2016 23:35:14) deeplink to this comment
This sounds like it is what I need, but I am not quite sure. I am just beginning to learn VBA, had some experience working with a program Boston Workstation that utilized some VBA, basically an interface that helped you build VBA scripts easier, but not experienced. Anyway, my issue is that I have a dashboard that is summarizing data the is pulled from multiple different pivot tables via the getpivotdata function. I have two caches, because I have to isolate some of the data from the original data, all the same data fields, just a subset with specific sorting and duplicates removed etc. I have two slicer's "Campus" and "PRIMARY_PAYOR" that all I want to do, is make the slicer's mirror each other, as in, if I select a location on the "Campus" slicer that is on the dashboard, the second campus slicer that is hidden on another tab is also changed to that location, that way, all the data showing on the dashboard is for that location, and same with the payor. Can you help me adapt this code for my purposes?
Comment by: Jan Karel Pieterse (28-6-2016 08:53:49) deeplink to this comment
Hi Allison, That is precisely what the code example is for. So yes that should be possible. What you need to know is the name of both slicers. Right-click the slicer and select Slicer settings. Record what is written next to "Name to use in formulas" and use that in the code.
Comment by: Ron (28-6-2016 14:19:56) deeplink to this comment
Hi Jan Karel, I used this code to manage 6 slicercaches with two slicers and it works great. There is one thing that is not working I guess: event looping. In my case the code is run 7 times where after the first run all slicercaches/pivots are already set to the filtering. I think it is due to having on one slicercache a third slicer (3rd level of detail only available in one pivot data set) but I can't get it to work. Any ideas?
Comment by: Jan Karel Pieterse (28-6-2016 16:20:39) deeplink to this comment
Hi Ron, You should be able to prevent event looping by using Application.EnableEvents = False in the beginning of the event sub. Don't forget to turn it back on at the end!
Comment by: Ron (28-6-2016 16:47:55) deeplink to this comment
Hi Jan Karel, I tried that but didn't work. The events are executed after the code Sub Workbook_SheetPivotTableUpdate has finished executing. So sequential events. It seems to be related to the amount of pivots related to a the slicercache of which the slicer changed. I have a sheet with 7 pivots based on one slicercache. When changing a slicer on that sheet it appears all pivots trigger the Workbook_SheetPivotTableUpdate sequential. On sheets where the slicer is related to one pivot the code is run only once. In both cases all slicers are synchronized and all pivots are updated.
Comment by: Jan Karel Pieterse (28-6-2016 17:21:56) deeplink to this comment
Hi Ron, Ah, yes, of course. One thing you could do is test for Target.Name inside the event and only allow the event code when it equals one pivottable's name.
Comment by: Allison (28-6-2016 17:41:34) deeplink to this comment
Ok, that is what I thought, I changed anything that say "year" to the name of my slicer "campus", however it doesn't seem to be executing? I don't get any errors or anything, it just doesn't seem to be calling the event.
Comment by: Ron (28-6-2016 18:19:58) deeplink to this comment
Hi Jan Karel, I don't understand. When testing for a valid pivottable's name all the 7 on that sheet will be considered valid and changed so triggering the event. Or am I missing something.
Comment by: Ron (28-6-2016 20:05:47) deeplink to this comment
I guess I solved the 7 pivots on one sheet with one slicercache by only allowing the first pivot on the active sheet to run the event code. Line added to run first in the event:
Comment by: Jan Karel Pieterse (29-6-2016 09:32:05) deeplink to this comment
Hi Ron, Excellent, that is what I intended.
Comment by: Jan Karel Pieterse (29-6-2016 09:33:03) deeplink to this comment
Hi Allison, Are you sure the event code is in the right place? (ThisWorkbook module)
Comment by: Ron (29-6-2016 10:23:43) deeplink to this comment
Hi Jan Karel, I also found a solution for the unwanted trigger when having pivot on different sheets having the same slicer. Just allow the event to run on the actual sheet where the slicer was triggered. This conludes in the following code to be included at the beginning of the event:
Comment by: Jerry (30-6-2016 04:13:21) deeplink to this comment
Jan, I have an xlsm file that previously contained several pivot tables and slicers. Our MAC users wanted to use the workbook so I removed slicers and pivotcharts associated with them to allow the MAC Excel 2011 users to use the application. I verified the slicers were removed with your slicer cache code below and it does not find any slicers in the workbook to report. However, the MAC users receive an warning/error message when opening the file: “This workbook includes content that is not supported by Excel for Mac 2011. *Slicer. Seems that even after deleting the Slicers some remnant is still in the workbook that Excel Mac 2011 is detecting. Any suggestion on determining what is causing this and how to eliminate it without rewriting the application from scratch.
Comment by: Jan Karel Pieterse (30-6-2016 07:05:42) deeplink to this comment
Hi Jerry, What if you remove the inner loop and just let it report the SlicerCache names?
Comment by: Jerry (30-6-2016 12:17:09) deeplink to this comment
Jan, Thank you! Not a MAC issue at all. Removing the inner loop did report a slicer that was located out of view I thought had been deleted and was not. Knowing the slicer name gave me a clue to which worksheet to search. Much appreciated! Thank You, Jerry
Comment by: Will Swan (6-7-2016 14:43:37) deeplink to this comment
NewB here. I got the function to return all items. However, it still says "all items" when I click on the selection in the called slicer using : =GetSelectedSlicerItems("Slicer_Response.") At this point, I have changed no coding. Thoughts?
Comment by: Jan Karel Pieterse (7-7-2016 10:29:48) deeplink to this comment
Hi Will, Possibly you have the wrong slicer name?
Comment by: William Swanigan (7-7-2016 16:45:11) deeplink to this comment
When I changed the name: =GetSelectedSlicerItems("Slicer_Response3.") the results given are: "No slicer with name 'Slicer_Response3.' was found" So, I believe the naming convention was correct.
Comment by: Jan Karel Pieterse (8-7-2016 19:54:36) deeplink to this comment
Hi William, The reason your code fails is that the function was written with pivot table slicers in mind, not Table slicers. I'll put this on my backburner to add to the article!
Comment by: Stephen (12-7-2016 15:48:10) deeplink to this comment
Hi, When printing a pivot table and slicers, I apply 'fit columns to one page' in the print option for obvious reasons but the slicer font doesn't shrink in proportion with the rest of the scorecard. Is there anyway of fixing this so that the slicer font is in proportion with the rest of the document? Thanks for any help. Stephen
Comment by: JonMorse (12-7-2016 22:38:13) deeplink to this comment
Jan, I have found alot of blogs about connecting Slicers to multiple data sources using VBA and pivot tables. In 2013, Excel added the slicer option to data tables. I have tried to find something that would allow me to filter two different data tables with some of the same column names using the same set of slicers (SlicerCache) but I can't get anything to work. I am not a real VBA guy but usually find that there is something that will help on the web. any thoughts?
Comment by: Jan Karel Pieterse (15-7-2016 16:53:27) deeplink to this comment
Hi Jon, I have a note to write about the subject, but it'll take some time before I'll get round to it I'm afraid.
Comment by: Jan Karel Pieterse (15-7-2016 16:54:48) deeplink to this comment
Hi Stephen, The way to adjust it is to create a new slicer style and adjust the font of the new style (one of the pages of this article shows how).
Comment by: Jagdish Negi (31-7-2016 15:55:49) deeplink to this comment
I have used the below formula for copying the selected I team from to excel however I am getting error #Name?. CUBERANKEDMEMBER("PowerPivot Data",Slicer_Brand,ROW()-ROW($E$4)) if I am using the same slicer name with in UDF then I am getting output but that UDF function is giving me coma separated output which I don't want. I need the output in the next Cell instead of coma separated... Thanks
Comment by: Fred Stimler (4-8-2016 21:42:25) deeplink to this comment
I am using Power Pivot and Excel 2016. I wish to have 4 pivot tables linked to several slicers and I think I know how to do that. I need to filter each pivot table to ONE record - all the same - the top record picked by the slicers. I then want to MOVE from first record to next..to last, etc. all while seeing only one record per pivot table. I know this is strange by I am simulating an existing report that printed takes 1/2 of a page. Any ideas will be helpful. I like your lessons. Fred
Comment by: Jan Karel Pieterse (9-8-2016 09:21:36) deeplink to this comment
Hi Fred, Perhaps this is a question we'd better discuss via email? My address is down below at the bottom of this page.
Comment by: Geoff Lilley (12-8-2016 17:56:29) deeplink to this comment
I love what you've done. I made a modified version, where I pass function arguments for: * What message to display for "multiple items" * How many items constitutes "multiple?" * What message to display for "all items" Couldn't have done it without you. Thank you.
Comment by: Jake (16-9-2016 02:03:06) deeplink to this comment
It may be that the fault is internal to my cranium - I am getting '#Name' - I have made sure I am using 'the Name to use in formulas' Apologies if this is a no brainer but have tried a few things and am not making progress - any help would be greatly appreciated
Comment by: Jan Karel Pieterse (16-9-2016 14:59:22) deeplink to this comment
Hi Jake, If you start entering the slicername in the formula, you should get Excel's intellisense dropdown showing you which slicer caches it has available for you.
Comment by: Justin (20-9-2016 00:41:46) deeplink to this comment
I'm getting the same error message as Jake no matter what I enter as the slicer name - either by typing the name in quotes or selecting the name from the dropdown.
Comment by: Jan Karel Pieterse (20-9-2016 06:51:40) deeplink to this comment
Hi Justin, What if you run this little macro from your workbook and try the names listed:
Comment by: marksu (23-9-2016 09:58:09) deeplink to this comment
hey Jan do you know how one can change the "name to be used in formulas" in VBA? somehow it works when I hardcode a string into the right place after the slicers.add function, but not when I use a string variable. any idea why this is the case? Kr Markus
Comment by: Jan Karel Pieterse (23-9-2016 14:08:07) deeplink to this comment
Hi Mark su, Have you got any example code to share? Even if it isn't working?
Comment by: markus (23-9-2016 14:18:46) deeplink to this comment
Yeah sure :) here you go: .Parent.PivotCaches.Create SourceType:=xlDatabase, SourceData:=transfer).CreatePivotTable _ TableDestination:=DestinationRange, TableName:=tblName .Parent.SlicerCaches.Add(.PivotTables(tblName), "CVP", "asdf").Slicers.Add ActiveSheet, , "CVP", "CVP", _ 1000, 800, 150, 200 these are the two lines, where I set up the pivot- and slicercache like posted it works but if I change "asdf" to a variable like slcName it throws an error. Kr Markus
Comment by: Jan Karel Pieterse (23-9-2016 16:36:37) deeplink to this comment
Hi Markus, Seems to me that this should work:
Comment by: Ashley (23-9-2016 17:25:39) deeplink to this comment
This is great! However, when using multiple slicers, if I then go on to choose a slicer within the original selection the cell link refreshes to All Items and my formulas no longer work. Is there a solution for this? Ashley
Comment by: Markus (23-9-2016 17:41:15) deeplink to this comment
Hi Jan Thanks for your reply. I also thought that should do the trick. But somehow it doesn't work. I just omitted the name attribute now, so there's no issue with that but the next time I iterate over this line it says "invalid procedure". When I go into debug mode and hit F8 to resume, it says "this slicercache already exists". It actually creates the slicercache but still throws an error. Any idea how I can resolve this? Kr Markus
Comment by: Jan Karel Pieterse (26-9-2016 10:14:02) deeplink to this comment
Hi Markus, Perhaps you can email a copy of the file?
Comment by: Jan Karel Pieterse (26-9-2016 10:15:14) deeplink to this comment
Hi Ashley, I'm not sure what you mean?
Comment by: Ashley (26-9-2016 14:09:58) deeplink to this comment
Hi Jan, Apologies, it's quite difficult to explain! I have a file containing costing data to be sent to various contacts but to be protected for their region only. I'm using your solution in a landing page for the contact to choose their area and enter a password. This then loads the data into a separate tab. However, there are multiple dependent slicers on this tab in which they can delve deeper into the data at site level for example. However, when they do this the formula on the landing page refreshes to display "All Items" throwing my password and formula off. Hope that makes sense and really grateful if you have a solution at all! Thanks, Ashley
Comment by: Jan Karel Pieterse (26-9-2016 17:56:25) deeplink to this comment
Hi Ashley, I'm afraid I'd need your file and some instructions on how to reproduce your problem.
Comment by: Ashley (27-9-2016 10:32:53) deeplink to this comment
Hi Jan, I'll have to create an example file as the data is confidential. How would I share this with you? Thanks, Ashley
Comment by: Jan Karel Pieterse (27-9-2016 11:20:53) deeplink to this comment
Hi Ashley, You can send it to as a reply to the confirmation message you got stating your reply was approved.
Comment by: Anastasiia (4-10-2016 16:24:29) deeplink to this comment
Hi, Thank you so much the macro is really helpful. I use this in one file with 4 slicers and it was great. But I have an issue with the other one. I have one Pivot based on one source and 8 more based on the other (common for all 8 pivots). I need to add 4 slicers which should work for all these pivots. With this VBA code I synchronized the slicers and they shows the same data but not exactly what I choose. Foe example, if I chooe 2016 as a year it shows the 2016,2015 and 2014. Or just 2016 and 2015. The same is for other slicers. I'm not very familiar with the VBA so could you please help me with this issue! Thank you
Comment by: Jan Karel Pieterse (5-10-2016 11:13:06) deeplink to this comment
Hi Anastasiia, If you like you can email your file to me and I'll have a look.
Comment by: Mads (18-10-2016 14:59:45) deeplink to this comment
Hi Jan I have a problem looping through all the filters in a slicer. Currently I am doing the loop on a single pivot table without using a slicer, but now I need to sort two pivot tables at once. The two pivot tables got data from the same source and it should sort the exact same thing for both tables and then save the sheet. So I thought that a slicer would be the easiest thing to do. but I can't make it work!! The following code works perfectly on a single Pivot table (pivotItem)! how do I convert it to do the same on a slicer? and it should only loop through customers starting with KUM as the code suggest.
Comment by: Jan Karel Pieterse (18-10-2016 16:58:05) deeplink to this comment
Hi Mads, Couldn't you use something like:
Comment by: Mads (21-10-2016 13:21:23) deeplink to this comment
Hi Jan, Thanks for the fast reply, it was really helpful! First, I can actually make the loop work, but it is very slow! Is there anyway to make it faster? ScreenUpdating is not doing the job. Second, I have no idea where to put my code, which copies and saves the sheet whenever it is a customer starting with "KUM". /Mads
Comment by: Jan Karel Pieterse (21-10-2016 14:36:43) deeplink to this comment
Hi Mads, For startes, you might try replacing this:
Comment by: Siam (26-10-2016 08:27:59) deeplink to this comment
How to manual sort slicer icon? i would like to mange it by my self.
Comment by: Jan Karel Pieterse (26-10-2016 20:55:43) deeplink to this comment
Hi Siam, One way to achieve that is by adding a custom list in the sort order that you need and set the slicer to use custom lists (default).
Comment by: Leo Meijer (4-11-2016 08:56:46) deeplink to this comment
Hello all In a PivotTable, you can click after filtering the filtered items, allowing you to see the underlying data will take on a new tab. Now I use multiple slices to filter a PivotTable. But I do not get the "slicers with filtered data" to see a new tab. Does anyone have an idea how I wanted to see the filtered data or receive? Thank you very much Leo Meijer
Comment by: Jan Karel Pieterse (14-11-2016 16:30:39) deeplink to this comment
Hi Leo, I assume your problem is that the data that gets dumped on the new tab is not filtered according to your slicer filters, correct? This happens to fields with slicers that are NOT a field in the pivottable. TO ensure you get filtered drill-down results, make sure your slicer fields are all in the pivottable (add them as page fields for instance).
Comment by: Ibrahim Omar (22-11-2016 18:54:11) deeplink to this comment
Thank you so much for this Jan! I want to do the same but for a slightly different case. Instead of synching 3 slicers, I have four pairs of slicers, and I need each pair to be synched. I have a pivot table with 4 slicers: Type, State, City & ZipCode. I also have a normal data table, not pivot, with the same fields and 4 identical slicers. I want any selection on the pivot table slicers to be synched in the data table slicers and vice versa. What would be the altered version of your code for this, given I don't understand VBA at all? Thanks so much in advance.
Comment by: Jan Karel Pieterse (22-11-2016 19:14:27) deeplink to this comment
Hi Ibrahim, I could write the code for you on a commercial basis if you like?
Comment by: John Bentley (24-11-2016 10:12:29) deeplink to this comment
Fantastic bit of code, really helped me out. Thanks! One thing that caught me out that this article may benefit from highlighting, where the code is looking at the slicer names -
Comment by: Paul Bell (24-11-2016 15:47:18) deeplink to this comment
Hi, I have a workbook with 3 sheets, "Complaint Chart", "Pivot Tables" and "Pivot Charts". The actual pivot tables, on the sheet Pivot Tables, are created from a dynamic named range from the Complaint Chart sheet. I then have 3 slicers, Year, Day and Type on the Pivot Chart sheet which are linked to all my pivot tables. The problem I have is when the userform adds new data to the Complaint Chart sheet the pivot tables refresh ok but the slicers appear to duplicate the 'filter options'. For example, The complaint chart sheet contains many rows containing the year 2016, however when new data is added to the complaint chart, with year 2016, the pivot tables refresh ok but the slicers then show 2016 twice in the 'filter options'. I cant figure out why this is happening or how to stop it, please can anyone help? Thanks in advance. Paul.
Comment by: Jan Karel Pieterse (25-11-2016 14:33:22) deeplink to this comment
Hi Paul, I strongly suspect there are both 2016 viewed by Excel as numbers and 2016 viewed by Excel as text.
Comment by: Joey (30-11-2016 16:41:52) deeplink to this comment
Hi, I have a pivot table with seven slicers and I used the UDF and it worked perfectly for six of the slicers. However, for the 7th one it always says "No items selected" regardless of if I have items selected or not. Not sure if this matters or not, but that slicer has a lot of items in it (over 10,000). Does anyone know why this is happening? Thanks.
Comment by: Jan Karel Pieterse (6-12-2016 10:08:37) deeplink to this comment
Hi Joey, I don't know. Perhaps that slicer has other differences?
Comment by: Ty (7-12-2016 01:30:16) deeplink to this comment
I want to resemble in my slicer as a dollar amount so it is consistent with the pivot table can this be done and how
Comment by: Filiep (7-12-2016 19:01:46) deeplink to this comment
I agree that slicers are nice but one thing that is truly handy with Pivot Filters is to be able to do a search for filter value. Especially when the list is long this is very handy; this option is not available for slicers I believe.
Comment by: Jan Karel Pieterse (8-12-2016 06:59:29) deeplink to this comment
Hi Ty, If you make sure the data is formatted as $ it should show up on the slicer as $.
Comment by: Giedrius (12-12-2016 12:03:10) deeplink to this comment
Hello, I have a slicer for years (2016, 2017) but in the list of available selections I also get "<2016-01-01" and ">2017-12-31". How do I get rid of them? I just need the years. Thank you.
Comment by: Jan Karel Pieterse (13-12-2016 06:59:54) deeplink to this comment
HI Giedrius, One way is by adding a calculated column to your sourcedata in which you calculate the Year.
Comment by: Whitney (15-12-2016 16:00:06) deeplink to this comment
I have inserted a slicer, the resource the slicer is using has multi colored fonts (for categorization purposes). When you double click on the value to induce the slicer information it does not transfer the font color from the original resource. Is there a setting or a way that this can be done?
Comment by: Jan Karel Pieterse (15-12-2016 16:02:49) deeplink to this comment
Hi Whitney, I'm afraid the font color of the source data cannot be reflected on a slicer. Only the number formatting (like currency symbols) will come through.
Comment by: David (22-12-2016 05:43:08) deeplink to this comment
Whoever wrote this tutorial just saved my last two months of work !! I wanted to synchronize Slicers, but I had this issue of all items selected when looping through the slicers. So I've made a macro running through each pivot filter table to change directly there, but the calculation ended up endless. Now you have explained the trick of selecting the last slicer item, I'm saved. Thank you very much ! David
Comment by: Dan (27-12-2016 07:42:10) deeplink to this comment
Hi Jan, Fairly new to the VBA and trying to implement the code for 8 slicers and pivot tables from 2 data sources....My slicers are Brand, Category and Manufacturer....Do i need to use Slicer_Brand, Slicer_Category...in the codes or ust plain Brand, Category ie...also where do i include the names of the pivot tables in the code? I am getting run time error 91...object variable or with block variable not set error. Thank you for your help
Comment by: Jan Karel Pieterse (30-12-2016 15:08:22) deeplink to this comment
Hi Dan, You can find out the name of a slicer from its settings(right-click a slicer, select Slicer Settings... look for "Name to use in formulas".
Comment by: Dan (4-1-2017 20:25:08) deeplink to this comment
Thank you Jan! For some reason when i modify the code and run it I get Run Time Error 1004 and below "oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True" is highlighted in yellow. What may cause this? Thank you
Comment by: Dan (4-1-2017 21:49:41) deeplink to this comment
Thank you
Comment by: Dan (5-1-2017 00:05:54) deeplink to this comment
Hi Jan, In my case the values in Slicer 1 differs from Slicer 2 what would be the workaround for this problem. Thanks
Comment by: Jan Karel Pieterse (5-1-2017 06:24:09) deeplink to this comment
Hi Dan, The RT error may be caused if your slicer is driving a Powerpivot pivot table or a pivot table based on more than one table. Maybe this suggestion helps: https://jkp-ads.com/articles/slicers04.asp?AllComments=True#23596
Comment by: Jan Karel Pieterse (5-1-2017 06:45:30) deeplink to this comment
Hi Dan, I guess that is up to you. If items are missing from slicer A, would you select them on B? I'd suggest a workaround where you add a third (hidden?) pivottable which contains all items of both slicers and use a slicer to that table as the main slicer to control the others. The slicers which are possibly incomplete should not be visible to the use IMO. To make it transparent to the user, I recommend to add visual clues (on the cell grid) if items are not available in a pivot table which do show on the slicer.
Comment by: Bruno (19-1-2017 18:02:35) deeplink to this comment
from you initial post "If you would want to connect the Quarter slicer to the Month slicer, you would need some VBA which, after selecting a month, sets the associated Quarter in the Quarter slicer." well, it's EXACTLY what I need. assuming I have everything on the same data set (a column for "quarter" e another for "month") is it doable? I just want to be able to set the month slicer and the quarter slicer adjusts automaticaly. Maybe you can point me in the right direction as I could not find it online for a VBA (very) newbie. Congrats on the excelent post. Bruno
Comment by: Jan Karel Pieterse (20-1-2017 11:35:02) deeplink to this comment
Hi Bruno, If the pivottables are connected to the same pivotcache, all you need to do is add both Q and M slicers and tie them to all pivottables. They'll sync automatically without need of VBA
Comment by: Bruno (20-1-2017 12:00:43) deeplink to this comment
Hello, thanks a lot for your fast response. you ALMOST solved my problem. I made 2 pivotables, one for quarter, one for month 2 slicers, one for quarter, one for month and connected either one to both pivot tables. it works, but only one way. if i choose quarter 1, the other slicer will adjust to the corresponding months (but they have to be selected first, or a clean filter) I would like to be able to choose month 1, or 2 and the quarter would adjust to the corresponding quarter (it does) with the total of the quarter (it doesn't). instead it presentes the same monthly value. i was testing as i wrote this and found a workaround. Added an extra column with quarter total for each month line and had that in the pivottable. problem solved.
Comment by: Jan Karel Pieterse (20-1-2017 15:39:22) deeplink to this comment
Hi Bruno, Ouch, I apologise, this is indeed not the right solution, as the Month slicer will filter for that month on all attached pivot tables. You would need something like this:
Comment by: Jan Karel Pieterse (20-1-2017 15:40:34) deeplink to this comment
Continued from previous message...
Comment by: Jan Karel Pieterse (20-1-2017 15:40:45) deeplink to this comment
Comment by: Bruno (20-1-2017 15:54:40) deeplink to this comment
Thank you very much. I managed with the workaroud that I mentioned but I'm DEFINITELY going to bookmark this for future use. Cheers, Bruno
Comment by: Peter (31-1-2017 14:16:12) deeplink to this comment
Hi. I can change the font in the slicers. But the "bottums" do not change size. Is there a way to change them as well. The reason why I change the fonts is to have the whole slicer to take less space when printed. Regards Peter
Comment by: Jan Karel Pieterse (31-1-2017 14:28:57) deeplink to this comment
Hi Peter, You can change the slicer directly from the ribbon and set the button height from there.
Comment by: Mitch Hollberg (22-2-2017 17:59:54) deeplink to this comment
Fantastic work! Thanks so much for posting this.
Comment by: Josh (23-2-2017 17:18:35) deeplink to this comment
Hi, I have a slicer that shows different account owners and their department spend. Is it possible to set a passcode similar to one in access, so that when each individual account owner receives this file they can only access their respectful department information?
Comment by: William Linn (24-2-2017 09:28:28) deeplink to this comment
This code is great, saved me lots of time, Thanks for your efforts.
Comment by: Jan Karel Pieterse (24-2-2017 15:05:51) deeplink to this comment
Hi Josh, In Excel the only way to do that is to send out the file with ONLY the information the user is allowed to see. This does not apply if the data is tied to e.g. a SQL Server database with proper user rights and permissions set up.
Comment by: Johan Karldén (7-3-2017 16:48:03) deeplink to this comment
This is a very good script, but what about if you use the data model as source? I can't get it to work with a pivot table created from PowerPivot. How can you fix that?
Comment by: Jan Karel Pieterse (7-3-2017 16:52:38) deeplink to this comment
Hi Johan, Have a look at this comment: https://jkp-ads.com/Articles/slicers05.asp?AllComments=True#23919
Comment by: Ben (10-3-2017 05:28:21) deeplink to this comment
Script works wonders when selecting a GROWING number of slicer items. But, if in sequence: All items are selected, Enable Multi Select, deselect any slicer item Items with no data then show up in output. Any idea why this might be happening?
Comment by: Jan Karel Pieterse (10-3-2017 11:39:27) deeplink to this comment
Hi Ben, Odd, seems to work fine for me. Can you send an example perhaps?
Comment by: K1S (14-3-2017 09:11:20) deeplink to this comment
@Geoff Lilley Hi Geoff, re: https://jkp-ads.com/Articles/slicers05.asp?AllComments=True#24354 Can you share those modifications please? Thanks
Comment by: Jan Karel Pieterse (14-3-2017 09:50:37) deeplink to this comment
Hi K1S, To set expectations: I don't expect Geoff is likely to notice your post as I don't send out any notifications to anyone else than the person who posted a comment.
Comment by: Richard (4-4-2017 16:45:48) deeplink to this comment
The event Workbook_SheetPivotTableUpdate(...) doesn't get called for me when I click on a slicer item. I am using Excel 2010, is that the reason?
Comment by: Jan Karel Pieterse (4-4-2017 16:48:47) deeplink to this comment
Hi Richard, No, that event should work. Have you placed it in the ThisWorkbook module?
Comment by: Richard (5-4-2017 10:32:21) deeplink to this comment
Hi I did try moving it to ThisWorkbook, but it made no difference. After reading a copious number of blogs on the subject I eventually changed the name to Worksheet_PivotTableUpdate(...) and, hey presto, it work. The problem then was that I had it calling a function to refresh a Pivot Table, so it got recursively called. Another trail round the blogs and found [B]Application.EnableEvents = True/False[/B] which solved that problem. So I have now got Slicer2 dynamically showing the items selected by clicking on Slicer1.
Comment by: Jan Karel Pieterse (5-4-2017 10:54:46) deeplink to this comment
Hi Richard, That is very odd, both on my Excel 2010 and 2016 it is called as written here on this page: Workbook_SheetPivotTableUpdate
Comment by: ks (5-4-2017 11:34:59) deeplink to this comment
Hi, I'm trying to adapt this so that it doesn't show a comma after a single or the last entry, and also that it can show "Multiple selected" if there are more than a given number of selected options, but I'm struggling to get it right. Could somebody help me please?:
Comment by: Jan Karel Pieterse (5-4-2017 11:51:08) deeplink to this comment
Hi KS, To remove the last comma (and space), just before the
Comment by: ks (5-4-2017 12:11:45) deeplink to this comment
Hi Jan, many thanks for your swift reply. I have it now like this:
Comment by: Jan Karel Pieterse (5-4-2017 17:23:02) deeplink to this comment
Hi ks, Try if setting CountNoDataItems to false in the worksheet function helps.
Comment by: ks (6-4-2017 08:23:09) deeplink to this comment
Hi setting CountNoDataItems to false makes "Multiple" work, but now "All" doesn't work
Comment by: Jan Karel Pieterse (6-4-2017 11:28:59) deeplink to this comment
Hi ks, HOw does this verison work for you?
Comment by: ks (7-4-2017 10:10:59) deeplink to this comment
Hi Actually it still doesn't work. With CountNoDataItems set to false it doesn't show "Multiple" items if lCt >3, and when set to true it shows "Multiple items" even if only 1 or 2 are selected.
Comment by: Alex (12-4-2017 06:26:44) deeplink to this comment
Hi, I would like to use one set of slicers to change two tables, not pivot, just two tables. Thanks!
Comment by: Jan Karel Pieterse (12-4-2017 11:27:12) deeplink to this comment
Hi Alex, Unfortunately, there is no VBA Event we can use that responds just to changing a filter using a slicer. Nor is there an event that responds to just filtering a table. So all you can do is things like: - Use the SheetDeActivate event on worksheets with your slicers and then synch the slicers on that de-activated sheet with the slicers on other sheets - Use the calculate event to do something similar (but this will likely ony work if formulas point to the tables)
Comment by: Jan Karel Pieterse (12-4-2017 11:32:43) deeplink to this comment
Hi ks, Apologies for the delay, I've been busy :-) This appears to work for me:
Comment by: Leona (14-4-2017 22:13:45) deeplink to this comment
Hi, I have four slicers linked to a pivot table. I need to count the number of selected items in one slicer, after items from the other slicers have been selected. Is there an easy way to do this? Thanks!
Comment by: Jan Karel Pieterse (17-4-2017 20:08:53) deeplink to this comment
Hi Leona, You could use the getselectedsliceritems function and use a regular worksheet function to count the returned number of text entries?
Comment by: Asha (19-4-2017 16:24:21) deeplink to this comment
How do I link a slicer to a pivot such that when I click the slicer(it is the first level of row labels as well) the pivot chart should expand to display the next level of row labels?
Comment by: Jan Karel Pieterse (9-5-2017 15:51:29) deeplink to this comment
Hi Asha, Slicers only apply filtering to a pivottable, they do not control expansion of pivot items. You could use VBA, more precise the Workbook_SheetPivotTableUpdate event in ThisWorkbook which gets triggered if you click a slicer and then figure out what to expand.
Comment by: Shane (12-6-2017 18:07:54) deeplink to this comment
I have a table as follows: Job Start Time Complete Time Script A 00:00:00 00:10:00 Script B 00:05:00 00:06:00 Script C 00:03:00 00:14:00 Script D 02:20:00 03:50:00 I need to be able to slice and display records by time ranges. For example: Show me all rows where Start Time >= 00:00:00 and Complete Time <= 00:12:00 Note: these time ranges are at the discretion of the user. Any advice would be much appreciated. Thanks!!
Comment by: Jan Karel Pieterse (13-6-2017 10:12:21) deeplink to this comment
Hi Shane, I'm afraid this can only be done using filtering, slicers are not up to a task like this (yet).
Comment by: Lasse Jensen (27-6-2017 16:15:38) deeplink to this comment
Just copy/pasted the code from this thread : Comment by: Jan Karel Pieterse (2/29/2016 6:03:21 PM) https://jkp-ads.com/articles/slicers05.asp?AllComments=True#23919 But I would like to skip the information related to the slicer name in the result, and only show the last part, this being the filtered value in question [Customer Geography].[Geography Country Name].&[United Kingdom], My PivotTable and slicer is based upon a data connection to SQL Server Analysis Services Cube structure
Comment by: Jan Karel Pieterse (27-6-2017 16:44:16) deeplink to this comment
Hi Lasse, You could change this line of code:
Comment by: Lasse Jensen (27-6-2017 16:50:47) deeplink to this comment
How about getting rid of the additional &[] in the filter value as well ???
Comment by: Jan Karel Pieterse (27-6-2017 17:39:34) deeplink to this comment
Hi Lasse,
Comment by: Lasse Jensen (28-6-2017 10:29:19) deeplink to this comment
Hi Jan This is awesome, - works perfectly, and a much better solution than the one CUBERANKEDMEMBER offers Many thanks Lasse
Comment by: Jan Karel Pieterse (28-6-2017 10:29:49) deeplink to this comment
Hi Lasse, Nice to hear you like it!
Comment by: Biprajit goswami (30-6-2017 12:49:21) deeplink to this comment
I tried running the above function with the slicer names suggested by excel intellisense but evrytime i get the output no slicer with name was found. Could you please help resolve this problem? Thank you
Comment by: Jan Karel Pieterse (30-6-2017 15:29:36) deeplink to this comment
Hi Biprajit, If you right-click the slicer and select Slicer settings the dialog shows two names. The name you need is the "Name to use in formulas"
Comment by: Michael Easterbrook (4-7-2017 19:39:06) deeplink to this comment
What if your slicer has 50,000 items? It would take a very long time to loop through every item.
Comment by: Jan Karel Pieterse (5-7-2017 11:12:49) deeplink to this comment
Hi Michael, Yes, that would make this quite slow unfortunately. Especially because the pivottables will refresh at each change in the loop.
Comment by: Biprajit goswami (6-7-2017 11:30:19) deeplink to this comment
The name to be used in formulae is the name suggested by the intellisense. Even using that in the function gives me the same result " no slicer with name was found"
Comment by: Jan Karel Pieterse (6-7-2017 14:03:58) deeplink to this comment
Hi Biprajit, You did put the slicername in quotes, like I show above?
Comment by: Biprajit (6-7-2017 19:03:25) deeplink to this comment
Thank you sir..after i put the name within quotes, i got the correct slicer selection.
Comment by: Geoff Lilley (7-7-2017 11:53:01) deeplink to this comment
The modifications I made are as follows:
Comment by: Jan Karel Pieterse (7-7-2017 11:54:07) deeplink to this comment
Hi Geoff, Thanks! I managed to publish your comment by removing some lines of text. TBH I don't know what triggered the spam filter :-)
Comment by: Gale (18-7-2017 17:30:35) deeplink to this comment
A noob here, whatever I do I only get the first value in each and every cell, could you show how the array formula looks for this and what cells does it refer to?
Comment by: Jan Karel Pieterse (18-7-2017 21:45:06) deeplink to this comment
Hi Gale, You must select a bunch of cells and then control shift enter the formula.
Comment by: Biprajit goswami (19-7-2017 10:57:59) deeplink to this comment
Sir, how do i modify the function if i have multiple slicers(6) and i want the selection name in one destination cell?
Comment by: Nelly (20-7-2017 09:35:49) deeplink to this comment
Hi all, Please advise if there is any option to customize the list of slicer buttons/item? For example, on the slicer I have 3 buttons (Public, Commercial and blank) and I want to see only Public and Commercial. Is there any option to delete/hide a button without changing the original source? many thanks
Comment by: Biprajit goswami (20-7-2017 11:31:24) deeplink to this comment
Sir If i have say 6 slicers and i want my selection from these slicers in one destination cell, what modification i have to do?
Comment by: Cesar (1-8-2017 20:16:31) deeplink to this comment
I have the GetSelectedSlicerItems code working. However, How do I get the values of the selected slicers values from the power pivot measures? I am using cubevalue. It returns values for single items. I don't know what formula to use for multiple selected items. This is the formula I was using. =CUBEVALUE("ThisWorkbookDataModel","[Measures].[FY18 Budget $]","[Table4].[Allocation Category].[All].["&E$10&"]"). It would pull E10 data if it doesn't include ",". But, I would like to pull data for multiple selected items.
Comment by: Jon (11-8-2017 19:59:41) deeplink to this comment
Hi, I create a pivot table on excel 2013 and I'm using the slicer option. First I have 3000 records, I'm sorting by region and I have the following results: Mexico - 5 results India - 48 US - 129 When I click on the 5 under Mexico, instead of showing me the 5 records from Mexico it opens the 3000 records. What did I do wrong? Please help. Thank you
Comment by: Jan Karel Pieterse (13-8-2017 17:04:57) deeplink to this comment
Hi Biprajit, You simply use the function pointing it to the cell containing the slicername: =GetSelectedSlicerItems(A2)
Comment by: Jan Karel Pieterse (13-8-2017 17:08:31) deeplink to this comment
Hi Nelly, You can't do that without modifying the data I'm afraid.
Comment by: Jan Karel Pieterse (13-8-2017 17:10:21) deeplink to this comment
Hi Biprajit, I would simply join them using 6 function calls: =GetSelectedSLicerItems("Slicer_1")&", "&GetSelectedSLicerItems("Slicer_2)&", "&GetSelectedSLicerItems("Slicer_3)&", "&GetSelectedSLicerItems("Slicer_4)&", "&GetSelectedSLicerItems("Slicer_5)&", "&GetSelectedSLicerItems("Slicer_6)
Comment by: Jan Karel Pieterse (13-8-2017 17:17:16) deeplink to this comment
Hi Cesar, You have to select as many cells (n=below each other) as you are expecting selected values. Then you enter your formula into those cells as one array formula by pressing control+shift+enter.
Comment by: Jan Karel Pieterse (13-8-2017 17:18:56) deeplink to this comment
Hi Jon, I'm afraid this is hard to trouble-shoot without the data or the file. You can contact me through the email address at the bottom of this page if you like?
Comment by: Jeff Weir (28-8-2017 04:49:53) deeplink to this comment
Hi Jan Karel. I see that there is a .VisibleSlicerItems property that you can directly iterate over, which will be faster than checking the .visible status of each and every .SlicerItem in the cache. I'm using this:
Comment by: Alex (11-9-2017 16:22:19) deeplink to this comment
Hi, This code works Great, thank-you! In my slicer I'm filtering Dates, If I wanted the cell result to show a date range instead of each date (ie. 1-Jan-17,2-Jan-17,3-Jan-17) would show as "1-Jan-17 thru 3-Jan-17" would that be possible?
Comment by: Jan Karel Pieterse (11-9-2017 17:13:32) deeplink to this comment
Hi Alex, One way I can get Excel to do that is by actually grouping the dates on a fixed number of days. Another would be to add a column which calculates the "period" from the date column and use that for the slicer.
Comment by: Hayden (11-9-2017 19:57:10) deeplink to this comment
(Working in Excel 2013) I have 2 connected Pivot Tables, 2 traditional slicers, and 1 timeline slicer. As it currently stands, the selection in the first slicer will effectively determine what options appear in the second slicer (ie. "Hide data with no value" works correctly). However, when I use the timeline slicer to choose a certain range of time, it does not remove options in the subsequent slicers that would now have no data matching the selected time frame. IS there a way to make the slicers recognize and respond to the timeline slicer's selection?
Comment by: Jan Karel Pieterse (12-9-2017 09:56:16) deeplink to this comment
Hi Hayden, Hmm, I wouldn't have expected that to happen indeed. IMO this is a bug.
Comment by: Abdul (3-10-2017 11:44:29) deeplink to this comment
How can I loop through slicer items. when I loop it keep on working with all items which are not in the slicer?
Comment by: Jan Karel Pieterse (3-10-2017 16:53:12) deeplink to this comment
Hi Abdul, Please show me the relevant piece of code?
Comment by: Luke (19-10-2017 03:20:19) deeplink to this comment
Hi - I have 4 slicers connected to one pivot table. I would like to show no data in the pivot table (except headers) until a selection is made from one of the slicers. Is there any way to do this without using VBA?
Comment by: Jan Karel Pieterse (19-10-2017 09:42:59) deeplink to this comment
Hi Luke, That would be hard to do as a pivot table does not allow you to filter itself to display no data at all to begin with. You can only filter it to show at least one existing category. That is, unless you change the source data of the PT after setting up that filter. If after a refresh of the data no applicable categories remain then your PT ends up without displaying data. So you try to could trick Excel into a state like than somehow.
Comment by: M.Matawie (14-11-2017 05:15:08) deeplink to this comment
So used to Excel 2003, I got frustrated when using Excel 2013. Real consolation will be the slicer. Fabulous! Made my filtering so much easier!!!
Comment by: Lucas Coy (20-11-2017 06:42:50) deeplink to this comment
Hi Jan, First of all, your code works fantastic! I do have a question though, is it possible to make it work on a "timeline" slicer? I've used your UDF on the timeline that I have in my sheet but it always returns "No Items Selected" even though it does have items selected. So I'm not sure if it's something I'm doing wrong or what. Thanks!
Comment by: Rita Lima (5-12-2017 02:54:07) deeplink to this comment
Hello Luke, Is it possible configure a background color of a Slicer item (Button) based on its value? For example, I would like show my slicer like: 0 - 30 (button green) 31 - 60 (button yellow) 60+ (button red) Thank you, Rita
Comment by: Jan Karel Pieterse (5-12-2017 15:57:08) deeplink to this comment
Hi Rita, I'm afraid that cannot be done.
Comment by: helen (15-12-2017 10:49:22) deeplink to this comment
Hello, I am trying to display an item that when clicked in my slicer it display it in another cell. I want to be able to do an if statement that so if certain things are selected then in another box then a message will display. thanks for your help. Helen
Comment by: Jan Karel Pieterse (15-12-2017 13:52:56) deeplink to this comment
Hi Helen, Can you elaborate? This page already shows how to get the selected items from a slicer?
Comment by: Kamil (3-1-2018 10:06:38) deeplink to this comment
Great solution, much appreciated, made my work much easier, thanks a mile!
Comment by: destinationsky (10-1-2018 12:43:09) deeplink to this comment
Hi, awesome post! I might have missed it but I´m trying to "substitute/control" a slicer with a dropdown. I am working with many pivots that I´d like to filter via a few dropdown´s as multiple slicer´s would be too cumbersome for my use. Any help would be greatly appreciated! Thanks
Comment by: Jan Karel Pieterse (10-1-2018 14:15:20) deeplink to this comment
Hi destinationsky, A drop-down only allows selection of one item, as I'm sure you are aware of. You could attach a macro to the drop-down, or if you use data validation in a cell (preferred), you could use the worksheet_Change event to trigger a macro that filters the proper field of the pivottable using the content of the cell or drop down. If you then add slicers tied to the same field, those slicers will ensure other pivottables will "receive" the same filter.
Comment by: Radich (31-1-2018 15:18:49) deeplink to this comment
HI ! im new in this and i trying to select just 1 parameter in my slice, i use .clearmanualfilter and then With ActiveWorkbook.SlicerCaches("VENDOR_ID") .SlicerItems("0177797197").Selected = True For Each slcMiSlicer In ActiveWorkbook.SlicerCaches("VENDOR_ID").SlicerItems If Not slcMiSlicer.Caption = strActivar Then If (slcMiSlicer.Selected <> True) Then slcMiSlicer.Selected = False End If End If Next slcMiSlicer End With the problem Vendor_ID hace like 15000 so its take a lot of time its any other way to clear all and just select 1 faster ? without if. thanks
Comment by: Jan Karel Pieterse (31-1-2018 16:33:39) deeplink to this comment
One thing you can try is adding another pivottable (which you add to the slicer as well!) with just one page field (VENDOR_ID) and then have the sync code write that single value to the cell of the page field directly.
Comment by: Robert Ricciardi (27-2-2018 14:40:44) deeplink to this comment
Good day, I have an exported data set with additional columns for an individuals name and assigned location added at the beginning. I would like to be able to utilize the slicer to have all individuals at the sliced location to show even if they have no data to count. What is happening is I can either get all individuals to show (regardless of the sliced location). Or I can get only the individuals with data to show (sliced location works here). Is there any way to get all data to show only from the sliced location? Any help would be greatly appreciated.
Comment by: Jan Karel Pieterse (28-2-2018 14:01:15) deeplink to this comment
Hi Robert, I'm not sure I understand, can you perhaps give a small example?
Comment by: Robert Ricciardi (28-2-2018 14:54:41) deeplink to this comment
I'm not sure I can give a sample due to confidentiality involving names and such. What I can tell you is that there are 4 linked pivot tables from 4 separate data sets (monthly, quarterly, semi-annual, and annual). These are counting entries of a particular test performed in the specified timeframe. Each of these sheets have the names and work locations of individuals in the added columns. I have added a slicer and reported relationships to the other tables. It is set to control the 4 pivot tables. However when the slicer is clicked for a particular location I see the following: * With the Field setting showing items with zero data, I get all employees from all locations and only the test count from the selected location. * With the Field setting NOT showing items with zero data, I only get the employees from the selected location with the count of tests entered. What I would like to accomplish is to have the show items with zero data, but only from the location selected. I hope this clears the question a bit. Thank you for your help and time, Robert
Comment by: Jan Karel Pieterse (1-3-2018 09:16:50) deeplink to this comment
Hi Robert, I have a hard time picturing your data but I do suspect what you need is not possible unless you change your data layout to one which has a separate table with people which is joined by adding a relationship.
Comment by: Tom T. (3-3-2018 03:34:40) deeplink to this comment
Good Evening, I have a Slicer that is a custom Calendar by Week. Works fine. However, for example we have February 2018 data that is “not ready” yet for the end user to view. Since there is data thought in the pivot tables the February 2018 are available for selection by the end user. Is there a way via VBA coding to hide or grey out this selection. For example they could only select the January 2018 slicers. We then would need to modify this code each month. Thanks in advance ! Tom T.
Comment by: Ranga (5-3-2018 08:13:57) deeplink to this comment
Great solution, however after setting it up to get the results from 12 slicers, it runs extremely slow. Sometimes it's taking over a minute to display the data. Any way to speed it up?
Comment by: Jan Karel Pieterse (5-3-2018 10:12:32) deeplink to this comment
Hi Ranga, I know, that is one of the draw-backs of this solution.
Comment by: Jan Karel Pieterse (5-3-2018 10:15:31) deeplink to this comment
Hi Tom, The way to solve that issue is to make sure the data is filtered from the source of the pivottable I'm afraid.
Comment by: Dashaun Huston (9-4-2018 18:06:24) deeplink to this comment
I copied the original function and it's not working as expected. I traced the code and GetSelectedSlicerItems is always "". Any Clue?
Comment by: Jan Karel Pieterse (10-4-2018 11:29:03) deeplink to this comment
Hi Dashaun, Does the code correctly find the slicerCache? Have you tried the built-in alternative function which only works for powerpivot slicers?
Comment by: Kevin Clarke (17-5-2018 21:28:13) deeplink to this comment
This is really helpful. I find you can speed up by toggling manual update on and off before updating the slicer selection. For Each pt In dstSlicer.PivotTables pt.ManualUpdate = True Next pt ... do the work For Each pt In dstSlicer.PivotTables pt.ManualUpdate = False Next pt
Comment by: Jan Karel Pieterse (18-5-2018 11:41:28) deeplink to this comment
Hi Kevin, Thanks!
Comment by: Don (21-6-2018 17:59:06) deeplink to this comment
Any idea why I would be getting #NAME? when I put the function call in a cell. I know that it means that it can't find that name, but I have triple checked to see that I'm using the right name in the call. I have slicers in tables and in pivot tables, and get the same response in both cases. I do use sql to get the original data, but then copy/paste it into Table1 and then drive pivot tables of that table, so no powerpivot (yet). Don
Comment by: Jan Karel Pieterse (22-6-2018 16:42:58) deeplink to this comment
Hi Don, Normally if you type the first opening quote Excel should show an autocomplete drop-down with valid names. Did you get that?
Comment by: Don (22-6-2018 16:58:15) deeplink to this comment
No. I tried on the Table tab and the Pivot Table tab (out of the table and pivottable area) and didn't see an autocomplete dropdown.
Comment by: Jan Karel Pieterse (25-6-2018 09:17:15) deeplink to this comment
Hi Don, I was referring to the situation where you are typing the formula directly into the cell. If you cannot get it to work, feel free to email a copy of the file to me. Make sure you refer to this conversation and (in the file) highlight where you are trying to achieve what.
Comment by: Jan Karel Pieterse (25-6-2018 09:20:23) deeplink to this comment
Hi Thomas, Hmm, good point, this timing popup should be an optional one indeed. I'll add it to the wish-list. Having thought about your process I think additing a new menu item to add reports to multiple workbooks is perhaps a good idea. What features would you expect such functionality to have?
Comment by: ErwinV (12-7-2018 20:13:43) deeplink to this comment
I'm getting a return of "No slicer with name '" & SlicerName & "' was found". I'm using the proper name from the Slicer settings. What could be wrong?
Comment by: Jan Karel Pieterse (12-7-2018 21:33:19) deeplink to this comment
Hi Erwin, I expect the name must be wrong, otherwsie you would not get that particular error message.
Comment by: Sohaib Aumer (17-7-2018 17:38:55) deeplink to this comment
Hi, Need help. I have 3 slicers in my dashboard, 1 filtering the pivot charts and other 2, non-pivot-tables. All 3 of those have different data sources and data, just a "city" column in common with the same cities. How can i use vba code to connect other 2 slicers to my 1st slicer. Regards, Sohaib
Comment by: Jan Karel Pieterse (18-7-2018 10:35:11) deeplink to this comment
Hi Sohaib, I think this can be pulled off, but I need some sample data. Can you email a sanitized copy of your file please?
Comment by: Jan Karel Pieterse (18-7-2018 11:23:08) deeplink to this comment
Hi Shohaib, I pulled this off my adding this code to the ThisWorkbook module:
Comment by: Tad Hammer (18-10-2018 16:20:37) deeplink to this comment
Jan Karel Pieterse- Thank you so much for all this information!!! I found the answer to a question I had about how to access a slicer item when you are connected to a cube. I had been searching and searching. Thank you so much!!! Best regards, Tad Hammer
Comment by: Jan Karel Pieterse (18-10-2018 16:49:52) deeplink to this comment
Hi Tad, You're welcome!
Comment by: IvoG (24-10-2018 15:29:31) deeplink to this comment
I'm getting a return of "no slicer found with name". I've searched the rest of the comments but can't find the solution here. I enter the slicer name with quotes: "Slicer_Woord1" as shown in the slicer settings name used for formula. I've used a normal powerpivot table. How can I fix this?
Comment by: Jan Karel Pieterse (24-10-2018 16:52:02) deeplink to this comment
Hi Ivo, It should work. Have you tried entering one of the CUBE functions, these take a slicer as their argument and the formula intellisense knows their names.
Comment by: IvoG (25-10-2018 09:18:25) deeplink to this comment
Hi Jan Karel, I've tried using the CUBE functions, they don't work either. When I start typing excel does recognize the name "Slicer_Woord1", but my data is not in a PowerPivot model so the CUBE functions won't work right?
Comment by: Jan Karel Pieterse (25-10-2018 11:06:21) deeplink to this comment
Hi Ivo, Correct, but it does mean you have the correct name for the slicer. Perhaps this is a table slicer, not a pivottable slicer?
Comment by: IvoG (25-10-2018 11:16:36) deeplink to this comment
Hi Jan Karel, I rarely use Excel, so I'm sorry if this sounds stupid. I've manually entered the data in Excel then I made a normal table from that data. I want to use this data to make a dashboard so from the normal table I clicked on summarize with Pivot-Table. The new Pivot Table has a Slicer. From that slicer I want to show the selecter words.
Comment by: Jan Karel Pieterse (29-10-2018 11:12:54) deeplink to this comment
Hi Ivo, That is precisely what is described on this page, doesn't it work for you?
Comment by: Carl Cooper (14-11-2018 14:30:55) deeplink to this comment
Great function. Thanks! I had to change the line: Set oSc = ThisWorkbook.SlicerCaches(SlicerName) to Set oSc = ThisWorkbook.SlicerCaches("Slicer_" & SlicerName) to get it to work.
Comment by: Miguel Herrera (25-11-2018 12:02:01) deeplink to this comment
It's a good solution and i have used it, However it consumes a lot of Excel & Pc resources since it is a Volatil application as declared in the code itself. This means that the macro will be recalculated whenever calculation occurs in any cells on the worksheet, making the computer slow. After many attemps i've found three methods that gets slicer selected items name. Firts one works based on Slicer using a macro (Not a UDF), Second works upon pivotTable directly and the thir one works with some formulas and extra entries in worksheet cells I know this is a long commented post but if someone is interested just let me know and I'll share
Comment by: Jan Karel Pieterse (26-11-2018 10:35:07) deeplink to this comment
Hi Miguel, Thanks! I agree it can be calculation-intensive.
Comment by: Ralph (4-1-2019 14:08:00) deeplink to this comment
Hi Jan Karel, I wanted to inquire about Miguel three solutions to your code. With the code being very calculation intensive, I wanted to explore other possible solutions.
Comment by: Andreas Katits (24-1-2019 10:22:05) deeplink to this comment
Hi, very nice article, but I miss one thing I want to share: If you want to find slicers only used on a specific sheet you can do the following to access the SlicerCache based on PivotTables
Comment by: f m (7-2-2019 11:01:21) deeplink to this comment
Hi there, when I try to edit the font size of my slider using your technique above of duplicating and then pressing modify, the modify button doesn't take me anywhere? It just remains on the same page? My spreadsheet default font is rather large so the slicers are fairly useless if I can't edit them! Can't see anywhere online where this has happened to anyone else? Using a Mac Pro - and when I press the modify button it just highlights blue and nothing happens.
Comment by: Jan Karel Pieterse (7-2-2019 12:47:09) deeplink to this comment
HI Frankie, I'm sorry, I d not own a Mac so I have no idea how this is supposed to work on that type of machine!
Comment by: Kerry (3-3-2019 04:29:41) deeplink to this comment
Hello Jan, Thank you for sharing this post. I have used it in 1 of my dashboards to insert graph title. I copied the UDF into a new workbook with just 1 pivot table and 1 slicer. When I past the formula into a sheet cell I get a #NAME error. I changed the slicer name. Any ideas to what I might be doing wrong? Thanks, Kerry
Comment by: Jan Karel Pieterse (4-3-2019 10:00:59) deeplink to this comment
Hi Kerry, My guess is that Excel cannot find the UDF properly. Where have you placed the UDF precisely?
Comment by: Kerry Austin (4-3-2019 14:07:48) deeplink to this comment
I inserted in a new module. It resolved by closing the excel files and rebooting. I think I had 2 workbooks open. I will monitor the situation. Thank you for sharing such a great code. Kerry
Comment by: Jan Karel Pieterse (4-3-2019 14:29:05) deeplink to this comment
Hi Kerry, Excellent, great you were able to solve it.
Comment by: Sai K Revuru (5-3-2019 20:48:53) deeplink to this comment
I would want to count the number of items that are selected in a slicer based on the selection of the other filter. the UDF simply displays the name that I have selected not the # of items that have been selected. would you know how I would get the just the # of items within the filter/slicer.
Comment by: Jan Karel Pieterse (6-3-2019 09:05:56) deeplink to this comment
Hi Sai, This function should work (not tested):
Comment by: Sai K Revuru (6-3-2019 16:10:08) deeplink to this comment
Thank you Jan for quick reply. It does count the items within the slicer. Here are the two issues. One, it doesn't change based on the other slicer selection. two, it counts all the items and not the items that got changed based on the others slicer selection. Basically I would want to show the grand total in a pivot table in a cell. As i change the filters , grand total changes. I am using slicer to do the same thing. any help would be really helpful.
Comment by: Jan Karel Pieterse (6-3-2019 17:20:46) deeplink to this comment
Hi Sai, Can't you get that grand total using GetPivotData?
Comment by: Sai K Revuru (6-3-2019 18:47:57) deeplink to this comment
Agreed. But I want to use slicer to get me the data since we are actively using slicers to show the total visually.
Comment by: Jan Karel Pieterse (7-3-2019 10:18:57) deeplink to this comment
Hi Sai, GetPivotData is guaranteed to be (a lot!) more efficient than a UDF which counts selected slicer items.
Comment by: Sai K Revuru (7-3-2019 12:15:28) deeplink to this comment
Thank you Jan. I think I will go with getpivotdata. Thank you so much for your help.
Comment by: Cat (18-3-2019 16:47:21) deeplink to this comment
First, THANK YOU for the function to return Slicer Selections. This code has been ESSENTIAL for a number of projects over the last year or so! My question now... I have a lengthy staff list and when the user "de-selects" a staff member, I do not want to display the 87 people _selected_ but only indicated which person is NOT selected. Is there an easy way to do that? and THANK YOU!!! cat
Comment by: cat (18-3-2019 16:54:25) deeplink to this comment
Ah! nevermind! I just wrote in asking about NON SELECTED... but then looked more closely at your awesome code and it was trivial to edit! I include here in case you'd want to correct me somehow... :) THIS IS AWESOME!!!!
Comment by: Jun (29-3-2019 03:37:34) deeplink to this comment
i still cant figure this out... does anyone have the code for the inquiry below Comment by: Maria (3/31/2015 1:03:16 PM) deeplink to this comment How would you modify the vba to display only available items? For example: Let's say you have 2 slicers, one CITIES and one SHIP COUNTRY. If you select "Austria" in SHIP COUNTRY, only a select number of CITIES are now valid as selection in the CITIES slicer. How do I list those available items? Thank you. Maria
Comment by: Jan Karel Pieterse (29-3-2019 14:39:56) deeplink to this comment
Hi Jun, Does this post help? https://jkp-ads.com/articles/slicers05.asp?AllComments=True#23850
Comment by: Jun (3-4-2019 00:57:51) deeplink to this comment
You Are AWESOME !!!!
Comment by: cat (31-5-2019 22:33:00) deeplink to this comment
And now... because this UDF is Volatile (and therefore slowing down my workbook), I wanted to make this into a SUB instead of a function... so now I can click a button to run this sub and return the selected items:
Comment by: Jan Karel Pieterse (3-6-2019 09:40:00) deeplink to this comment
Hi Cat, I'm not sure why you think the function is volatile, it shouldn't be (there is no Application.Volatile in there).
Comment by: Marcel Eggen (20-6-2019 15:40:00) deeplink to this comment
I am not sure if the above is what I am looking for but I am looking for a solution for the following problem. I have a table with a number of columns. One of the columns contains the region where a specific task is being executed. An other column also contains the region where a follow-up task is being executed. Both regions can differ from each other. Both colums have obviously their own slicer. What I would like to achieve is that whenever the first slicer is being activated with a region the second slicer gets the same setting. So both slicers in the example below would be set to "Midden" by just clicking on the first slicer. With ActiveWorkbook.SlicerCaches("Slicer_RegioKantoorBehandelaar") .SlicerItems("Midden").Selected = True .SlicerItems("").Selected = False .SlicerItems("Noord Oost").Selected = False .SlicerItems("Noord West").Selected = False .SlicerItems("West").Selected = False .SlicerItems("Zuid").Selected = False End With With ActiveWorkbook.SlicerCaches("Slicer_RegioKantoorOpnieuwBehandelaar1") .SlicerItems("Midden").Selected = True .SlicerItems("").Selected = False .SlicerItems("Noord Oost").Selected = False .SlicerItems("Noord West").Selected = False .SlicerItems("West").Selected = False .SlicerItems("Zuid").Selected = False End With
Comment by: Jan Karel Pieterse (20-6-2019 17:41:00) deeplink to this comment
Hi Marcel, Yes, you could use very similar code to what is shown on this page.
Comment by: Marcel (21-6-2019 09:07:00) deeplink to this comment
Hoi Jan Karel, Bedankt voor de snelle reactie. Ik ga het artikel eens goed bestuderen.
Comment by: Marcel (21-6-2019 14:30:00) deeplink to this comment
I took a good look at your code, adjusted it to work in my sheet and I am impressed. Works great. This is indeed was I was looking for. However clicking on one slicer and synching the matching other slicer takes up to 27-40 seconds depending on which Region in the slicer was selected. What could be causing this huge amount of time. I have - About 9000 records, 83 columns - 11 slicers - Only 2 slicers are synched with your script - Dozens of PT's - All combined in a dashboard
Comment by: Jan Karel Pieterse (21-6-2019 17:08:00) deeplink to this comment
Hi Marcel, I'm not sure. But each change in filter will cause an update of all of the attached pivottables, so it'd make sense if it would take a while if there are many pivottables attached to the two slicers. This could probably be better solved by making sure there is only one slicer to filter on rather than having to synch two!
Comment by: Zak (29-8-2019 14:09:00) deeplink to this comment
Hi Jan, I have a table with 2 slicers and 2 pivot tables with slicers The 2 pivot tables along with 3 others(these 3 do not have slicers) count items from 2 columns in the original table and then display as graphs I want the 2 slicers from the table labelled Region and Country to link to the 2 pivot table slicers of the same name The pivot table slicers are called Slicer_Region and Slicer_Country The table slicers are called Slicer_Region1 and Slicer_Country1 How can I modify your code to pull this off
Comment by: Jan Karel Pieterse (29-8-2019 15:51:00) deeplink to this comment
Hi Zak, I have created a setup where a pivottable has a slicer named Slicer_Shipcountry1 and a table has a slicer named Slicer_Shipcountry2. To have the PT slicer update the table slicer:
Comment by: Zak (29-8-2019 16:18:00) deeplink to this comment
Hi Jan, I assume if there are multiple slicers on each table type, it would just be more variables and the following code repeated with the new variables, oSc3, oSc4, oSc5, etc
Comment by: Jan Karel Pieterse (29-8-2019 16:43:00) deeplink to this comment
Hi Zak, Yes that would work. Alternatively you could move the code out of the Pivot event into a normal module, adding the slicernames as arguments. Then you could pass the slicernames as arguments to the new sub. So the heading of the new sub would look like:
Comment by: LEO MANSI (18-9-2019 15:42:00) deeplink to this comment
This code is great when the slicer is linked to a PivotTable. But when the slicer is linked to a Table, the function returns "No slicer with name 'Slicer_ProposalType' was found", where Slicer_ProposalType is the name of my slicer. Is there a way to get it to work with a slicer that is linked to a table? Thank you!
Comment by: Jan Karel Pieterse (18-9-2019 15:55:00) deeplink to this comment
Hi Leo, This one works fine for me:
Comment by: LEO MANSI (18-9-2019 16:24:00) deeplink to this comment
Thank you! Works great
Comment by: Chams (21-9-2019 13:52:00) deeplink to this comment
Hi JAn, Last code for slicer of normal table is always give error feedback when used as function ?? what might be the issue. I convert the function into normal sub to run in command bottom then it worked fine, what might be the issue?
Comment by: Jan Karel Pieterse (23-9-2019 11:02:00) deeplink to this comment
Hi Chams, Are you referring to this comment: https://jkp-ads.com/Articles/slicers05.asp?AllComments=True#29198 What kind of error are you getting precisely?
Comment by: Jamie (7-10-2019 17:41:00) deeplink to this comment
HI Jan Your last piece of code works great with the table I've assigned to my pivot. Although it brings back [table].[column].[Data] . Is it possible only to show Data in the returned cell ? ..
Comment by: Jan Karel Pieterse (8-10-2019 21:23:00) deeplink to this comment
Hi Jamie, You coule use the Split function in VBA to split the returned text by the period and then only return the last item of the array Split gives you:
Comment by: Kristen Lindloff (21-10-2019 22:56:00) deeplink to this comment
I saw this asked, but didn't see an answer. I am using both slicers and a timeline. Is there a way to get this to work with the timeline as well?
Comment by: Kristen Lindloff (22-10-2019 07:30:00) deeplink to this comment
I have multiple slicers, and some of them have (blank) values depending on what other slicers are set to. When that happens i get part of the slicer name as the result. How can I get it to say (blank) instead? Here's the resulting text as an example: recordstatus].&, The current code I'm using:
Comment by: Jan Karel Pieterse (22-10-2019 11:41:00) deeplink to this comment
Hi Kristen, You could do something like this:
Comment by: Jan Karel Pieterse (24-10-2019 13:25:00) deeplink to this comment
Hi Kristen, I must say I haven't had a look at timeline slicers yet.
Comment by: Kristen (25-10-2019 17:06:00) deeplink to this comment
I found a workaround for getting the information off of a timeline slicer. This will be able to give you the minimum & maximum dates used on the slicer to be able to reference in cells or equations. https://sfmagazine.com/post-entry/december-2017-excel-creating-tables-from-timeline-slicers/ There's no VBA coding required!
Comment by: Philip (25-11-2019 11:08:00) deeplink to this comment
Hi Jan, Thank you for all your solutions to complex problems. I have a worksheet setup to hold all slicers in one place which update quite a lot of number pivot tables. I had the same problem with performance. I use a routine on the pivot cache with the master slicers to turn off/on ManualUpdate on all pivot tables in the workbook. Performance is acceptable now. I also show the user that the report is being updated with a form.
Comment by: Jan Karel Pieterse (25-11-2019 12:04:00) deeplink to this comment
Hi Philip, Thanks!
Comment by: Karan (3-12-2019 14:23:00) deeplink to this comment
Hello Jan, Is there any way I can select only specific items in a slicer? For example, let's say that there is a slicer for "City" and I only want to select cities with a population of more than 500 (assume the population is also part of the data). Can I write a VBA code to select just those cities?
Comment by: J.K. Pieterse (11-12-2019 11:43:00) deeplink to this comment
Hi Karan, Apologies for the belated reply. A slicerItem in a slicer object has a HasData property which is false if that item of the slicer would cause the pivottable to be empty when selected. To use that information, you must first filter your pivottable on the "population more than 500" condition.
Comment by: Lois (6-3-2020 11:20:00) deeplink to this comment
Hi Jan, I used the VBA code and it seems to answer "No items selected" even though some items are selected. The excell worksheet does contain a Power Pivot link, so could this be the reason it doesn't work?
Comment by: Jan Karel Pieterse (9-3-2020 10:17:00) deeplink to this comment
Hi Lois, Have you tried the CUBERANKEDMEMBER function I mentioned above?
Comment by: Kim (29-4-2020 23:25:00) deeplink to this comment
Is there a way to disable right click on slicers? I don’t want end user to manipulate my data, specially the “report connection” . Please help me solve this..thank you!!
Comment by: Jan Karel Pieterse (30-4-2020 11:37:00) deeplink to this comment
Hi Kim, I tried to do that, but I failed. If you protect the sheet and set the slicer to locked, the slicer no longer works. The only way to achieve this is by adding ribbon customisation to the workbook and disabling the commands you don't want to allow access to. Even then, I bet there is still a way to get at what the user wants to do. These things are hard to protect a user from doing. What you might do is add some VBA that checks the slicer settings and restores them to what you want them to be.
Comment by: Kim (30-4-2020 13:53:00) deeplink to this comment
How about, disabling report connections... on context menu? I am a newbie and I dont know how to do it..please help me, I appreciate your reply, thank you so much!! You helped me a lot too with the project I’m currently working on
Comment by: Jan Karel Pieterse (30-4-2020 14:15:00) deeplink to this comment
Hi Kim, Sure. All you need is to add some RibbonX XML to your file:
Comment by: Amin (10-6-2020 13:20:00) deeplink to this comment
Thank you!
Comment by: Axel (12-8-2020 08:34:00) deeplink to this comment
Beste Jan, Ik heb een Performance Dashboard gemaakt waarbij ik meerdere draaitabellen heb gekoppeld aan 1 slicer. Nu heb ik gisteren een extra draaitabel gemaakt, welke ik ook aan dezelfde slicer wil koppelen. Echter komt deze 'draaitabel 7' niet bij de rapportverbindingen te staan in de slicer. Weet jij hoe ik deze draaitabel toch kan koppelen deze ene slicer? Met vriendelijke groet, Axel Tichelaar
Comment by: Jan Karel Pieterse (12-8-2020 14:16:00) deeplink to this comment
Hallo Alex, Daarvoor dient de draaitabel dezelfde draaitabelcache te gebruiken. Als dat niet het geval is, zie dan de volgende pagina van dit artikel.
Comment by: Jessica (24-8-2020 01:45:00) deeplink to this comment
Is it possible to group slicer values into buckets/bins, without creating additional columns in the source data? I have a slicer with values or 0-100. Instead of having 101 numbers in the slicer, I would like to be able to select 0-9, 10-19, 20-29, etc. Any ideas are appreciated! Thanks!
Comment by: Jan Karel Pieterse (24-8-2020 10:22:00) deeplink to this comment
Hi Jessica, If you group the field the slicer is attached to, the slicer will become grouped as well.
Comment by: Christy (31-10-2020 11:09:00) deeplink to this comment
Is it possible to create Common Slicer for main table and its transposed version?
Comment by: Jan Karel Pieterse (2-11-2020 11:06:00) deeplink to this comment
Hi Christy, I'm not entirely sure what you mean. DO you have a table which you've transposed using the From table button on the Data tab? If so, the answer is yes!
Comment by: Michel Tremblay (5-1-2021 17:59:00) deeplink to this comment
A huge thank you for the udf function. She solved all my problems
Comment by: Jan Karel Pieterse (5-1-2021 18:01:00) deeplink to this comment
Hi Michel, You're welcome!
Comment by: Don (21-1-2021 18:52:00) deeplink to this comment
Hello Jan, I have 2 Queries (Ole DB) that I use to create 1 pivot table each (PivotTable1 & PivotTable2) on the same worksheet (ptForecast), with each pivot table having 1 slicer for 'Department' (Slicer_Department and Slicer_Dept), so it has 2 separate pivot caches. The items in the 2 slicers for Department/Dept have the same list of items. I would like to control both PivotTables with one slicer. I have tried numerous variations of the code you initially provided and others have asked about in the comments, but so far haven't been able to come up with a solution that updates Slicer_Dept with Slicer_Department's item. Thanks, Don
Comment by: Jan Karel Pieterse (22-1-2021 18:12:00) deeplink to this comment
Hi Don, If you like you can email your attempt to me and I'll see if I can fix the code for you, OK?
Comment by: Marit (10-2-2021 16:04:00) deeplink to this comment
Hello, Is it possible to "wrap" text in a slicer ? Or another method to present text in more than one line in the slicer ? Marit
Comment by: Jan Karel Pieterse (11-2-2021 13:54:00) deeplink to this comment
Hi Marit, I'm afraid not!
Comment by: Ranga (15-2-2021 22:00:00) deeplink to this comment
I've had a degree of success using this this code to show slicer selections. However, it doesn't quite suit my needs. I'm looking for a method to display only items selected or deselected, i.e. show me what the user has clicked on. Using the mentioned code, if I deselect an item using control-click it displays all other selections - not ideal when deselecting one item from hundreds. Any suggestions?
Comment by: Andreas (19-2-2021 08:15:00) deeplink to this comment
Hi Jan, first of all: a great thank you for this piece of work. It really helps a lot. But is there a possibility to "autoselect" displayed sclicer items? E.g. you have a dependency by selecting "Europe" and your ShipCountry Slicer would only display "Austria, Belgium, France..." but not the others. Now I want to select those displayed automatically. Thanks in advance.
Comment by: Jan Karel Pieterse (23-2-2021 10:16:00) deeplink to this comment
Hi Andreas, I think you want the slicer to turn off its filter when the selection changes of another slicer? So for example: if the another country is chosen, the filter from cities is removed?
Comment by: Jan Karel Pieterse (23-2-2021 10:25:00) deeplink to this comment
Hi Ranga, If you expand the page to display all comments: https://jkp-ads.com/Articles/slicers05.asp?AllComments=True You'll see there are quite a few iterations of the function for certain situations. I suggest you scroll through them and try a couple of variations.
Comment by: Andreas (23-2-2021 10:34:00) deeplink to this comment
Hi Jan, that is not the problem. I wanted to select the displayed items automatically, if the filter changed. But the good slicers in Excel are, the bad are the possibilites in VBA. I solved this another way with a PivotTableUpdate event. Thanks
Comment by: Jan Karel Pieterse (23-2-2021 11:13:00) deeplink to this comment
Hi Andreas, I'm not sure what it is you wanted, but if you've solved it there isn't much point for me to try to understand is there :-)
Comment by: Alexius Galloway (18-3-2021 21:13:00) deeplink to this comment
I use and love this function, thank you; however, I would like to know how to custom sort when multiple items are selected. For my current situation, user will select between 1 and 7 items. Other tables may be between 1 and 4, 1 and 15 items...but again, for today 1 and 7 items.
Comment by: Jan Karel Pieterse (19-3-2021 13:58:00) deeplink to this comment
Hi Alexius, What exactly do you have in mind?
Comment by: Felipe Mondaca (20-4-2021 15:17:00) deeplink to this comment
Hi, I'm a trainee student of Chem. Engineering, I've never thought I was going to need this kind of VBA. I found the link to this topic in a Youtube comment, and I'm so glad for that. You helped so much to me with this, and I just want to say thank you so much.
Comment by: Jan Karel Pieterse (20-4-2021 17:30:00) deeplink to this comment
Hi Felipe, I'm glad I was able to help you!
Comment by: Cam (1-10-2021 12:17:00) deeplink to this comment
Hi Jan and everyone. I have an issue with my slicers. Basically I have a dashboard with 5 graphs coming from 5 Pivot Tables. 4 of them come from the same base table, but one comes from a different one. I don't think it's possible to combine them. Both tables have a dataset in common: area code. I would like that when I select a number in my slicer 1, the slicer 2 selects the same one automatically. How would that look in a VBA code ? Thank you !!!
Comment by: Jan Karel Pieterse (1-10-2021 13:30:00) deeplink to this comment
Hi Cam, I'd say "it depends". If you use the data model for the pivot tables, you can add an in-between table which contains all unique area codes. If you then set the correct relationships between the tables you should be able to use the new area code in both your pivot tables and slicer to filter then with one slicer. Clear as mud?
Comment by: Andy Murdoch (13-12-2021 12:02:00) deeplink to this comment
Thank you for this function. I would like to know how to do the opposite of what's being achieved. How do I change it to show the items that have been filtered out?
Comment by: Jan Karel Pieterse (13-12-2021 13:17:00) deeplink to this comment
Hi Andy, Basically you replace this line:
Comment by: Morgan (23-12-2021 17:07:00) deeplink to this comment
I have used this solution for several years in a file that I keep, but this week I started to get some issues and was hoping you could help. I have the formula referencing my Slicer name =GetSelectedSlicerItems("Slicer_Fund_Name) but when I enable the document, I get an error in the VBA debugger that says : Compile Error: Cant Find Project or Library and its highlighting the Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2) section. Any suggestions?
Comment by: Jan Karel Pieterse (23-12-2021 17:41:00) deeplink to this comment
Hi Morgan, When you get that error, click OK. Then click Tools, References. Are any marked as MISSING?
Comment by: Peter Roberts (15-1-2022 09:06:00) deeplink to this comment
If I have a table of accounting transactions for 12 months data in a pivot table Is it possible to create a slicer that displays a column with the total for month 3 and a second column with the total year to date [ie ytd]
Comment by: Jan Karel Pieterse (17-1-2022 11:44:00) deeplink to this comment
Hi Peter, Slicers can only contain the items from an existing field (column) of the source data of your pivot table. Displaying (sub)totals in a slicer is not possible.
Comment by: Markus (28-1-2022 19:53:00) deeplink to this comment
This is a very nice solution! Two questions... 1.) Is it possible to get the filter / chosen duration of these time span slicers too? E.g. Jan2021 to Aug2021 2.) Is it possible to get the results in individual rows, e.g. by using that function as a kind of array formula or any other kind? So to see the filter values not comma separated but each in a single line?
Comment by: Jan Karel Pieterse (31-1-2022 10:19:00) deeplink to this comment
Hi Markus, Don't know if a timeline slicer allows us to access it's data. Perhaps you can try? In the comments you can find several examples of UDFs returning an array, for example: https://jkp-ads.com/articles/slicers05.asp?AllComments=True#29198
Comment by: Judy (9-4-2022 02:57:00) deeplink to this comment
I am trying to find a way to limit a slicer to single select. I need to Disable the multi select and CTRL button so the users cannot circumvent the Single Select as the ONLY select. I have a Data Entity that needs the single select and it filters 3 other slicers in turn. Is there any way to limit the slicer as a single select, or maybe use a single select drop down that will filter the slicers? With or without VB code. Thanks in advance...
Comment by: Jan Karel Pieterse (11-4-2022 10:22:00) deeplink to this comment
Hi Judy, There is no way to limit a slicer to just one item, But what you can do is this: - Add a pivottable to the same slicer in some out-of-the-way location which has the slicer field as a row item - Write a formula to count the # of filtered rows - Use a formula in a cell next to the slicer that displays red text as soon as more than one item is selected in the slicer.
Comment by: PhilC (29-6-2022 05:08:00) deeplink to this comment
Is there a way to dynamically get a slicer name to use in a CUBE formaula? I have a template sheet that I want to duplicate "x" number of times in a single file. The slicer name is used for CUBE formulas. When the tab is duplicated, the slicer name changes so the formulas return the values on the original Template sheet, not the duplicated one. Thanks in advance Phil
Comment by: Jan Karel Pieterse (11-7-2022 11:21:00) deeplink to this comment
Hi PhilC, I don't think there is an easy way to do this, you must edit the formula to use the correct slicer name. You can however move the slicer name to a separate cell on the sheet. In order to make it work, the cell must contain a formula with just the name of the slicer preceded by the = sign. For example:
Comment by: Christopher W (26-7-2022 11:14:00) deeplink to this comment
How can I change the colour of each slicer button to match the colour of the line/column/bar>
Comment by: Jan Karel Pieterse (26-7-2022 11:38:00) deeplink to this comment
Hi Christopher, You cannot change the color of individual slicer buttons I'm afraid, just the color of all buttons.
Comment by: Wim Gielis (29-7-2022 01:17:00) deeplink to this comment
Slicers can be tied to tables (ListObjects) but 1 slicer operates only on 1 such table. It would be very useful if 1 slicer can filter in several tables, simply in Excel.
Comment by: Jan Karel Pieterse (29-7-2022 11:29:00) deeplink to this comment
Hi Wim, I totally agree!
Comment by: Anna Pauley (25-10-2023 20:06:00) deeplink to this comment
How do you modify the below code to connect another slicer?
Comment by: Jan Karel Pieterse (26-10-2023 11:25:00) deeplink to this comment
Hi Anne, In the code there are two lines which synchronize two other slicers:
Comment by: zoey (5-3-2024 04:37:00) deeplink to this comment
Hi there, Currently i have 4 slicers for 4 pivotables(all with different datasource), i want to control these 4 slicers at the same time. Below are my code, it works for slicer 2 now, but get erorr "invalid call prodedure or aurgument" for slicer 3 and 4, any idea why? Slicer 1 and 2 are on the same sheet, 3 & 4 are on the other sheet.Thank you so much!
Comment by: Jan Karel Pieterse (5-3-2024 15:48:00) deeplink to this comment
Hi Zoey, Which line of your code is yellow when you click Debug?
If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.
Please enter your name (required):
Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):
Your request or comment (max 2000 characters):
To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].
I give permission to process this data and display my name and my comment on this website according to our Privacy Policy.