Subscribe
in a readerWhy Slicers
If you've ever done filtering on Pivottables before you'll know why. But I'll spell it out here in case you haven't.
With any version of Excel, you can filter the content of your Pivottable report (or chart for that matter) by clicking the appropriate dropdown and checking/unchecking boxes:
Old-style Pivottable filtering
This method has a number of drawbacks listed below, in no particular order:
- Users who are not familiar with Pivottables are easily confused by the filter dropdowns.
- To check/uncheck an item you must click in the checkbox, clicking the item only selects the item, it does not change the check.
- You cannot drag across the items to select a number of them.
- You cannot modify the appearance of the filter, apart from changing the size of the entire box.
- There is no way of telling which filters are in effect, only that there is some filtering applied.
- If you have more than one Pivottable tied to the same data, then applying a filter to Pivottable 1 does not necessarily mean Pivottable 2 gets the same filter settings; it takes VBA to keep them in synch automatically.
- If your report has hierarchical data (countries and cities for example), then a filter on a higher level field in the hierarchy will not change the items visible in the lower fields. So Excel will happily show Toronto, even when you've unchecked Canada in the country filter. This makes filtering on large Pivottables very cumbersome.
Slicers put an end to a number of these problems:
Slicer Pivottable filtering
- They are very intuitive to use.
- You can click an item, drag across a number of them, use control or shift click, in other words they are easier to use.
- The appearance of a slicer is very configurable.
- You can tie one slicer to as many Pivottables as you like, keeping them in sync.
- You can have multiple "copies" of a slicer operating together on different worksheets.
- Slicers reveal hierarchical data in a sensible manner: non-applicable values are shaded differently so you'll know they do not apply in the current filter mode.
Next: Changing slicer settings
Comments
All comments about this page:
Comment by: Chris (8/21/2012 12:08:12 AM)
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: Shankar M (1/13/2014 11:07:48 AM)
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 (1/13/2014 11:43:54 AM)
See this page:
http://www.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: Filiep (12/7/2016 7:01:46 PM)
Comment by: Alex (4/12/2017 6:26:44 AM)
I would like to use one set of slicers to change two tables, not pivot, just two tables.
Thanks!
Comment by: Jan Karel Pieterse (4/12/2017 11:27:12 AM)
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)
Have a question, comment or suggestion? Then please use this form.
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.