Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Pivottable Slicers > Why Slicers

Why 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:

Filtering a Pivottable field
Old-style Pivottable filtering

This method has a number of drawbacks listed below, in no particular order:

Slicers put an end to a number of these problems:

Filtering a Pivottable field with slicers
Slicer Pivottable filtering

Next: Changing slicer settings

 


 


Comments

All comments about this page:


Comment by: Chris (8/21/2012 12:08:12 AM)

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: Shankar M (1/13/2014 11:07:48 AM)

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 (1/13/2014 11:43:54 AM)

Hi Shankar,

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)

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: Alex (4/12/2017 6:26:44 AM)

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 (4/12/2017 11:27:12 AM)

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)

 


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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].