Use Slicers to Filter a Table in Excel 2010

Pivot table filtering was improved in Excel 2010, when Slicers were introduced. Instead of using the drop down lists in the pivot table headings, you can click on a Slicer, to quickly filter the pivot table.

Slicers take up some room on the worksheet, but you can quickly see what filters have been applied. And, unlike Report Filters, Slicers show you what is available in the other fields, after you have applied a filter.

Slicers for Excel Tables

In Excel 2013, Slicers were enabled for named tables too, so you can filter your data with a single click. They work just like Pivot Table Slicers, and are especially handy if you’re doing a presentation. You can click one of the big Slicer buttons, instead of fumbling through the filter drop downs.

Workaround for Excel 2010 Tables

Slicers don’t work on Excel 2010 tables, but if you’re using that version, there’s good news – AlexJ has developed a workaround.

There are a couple of limitations:

You need a unique identifier in each table row.

Changes made manually to the field filters on the table are not reflected on the slicers (you might want to hide the table filters)

Add a Pivot Table and Slicers

From the Excel table’s data, AlexJ built a pivot table, with the ID field in the Row Labels area. Next, he added two Slicers for the pivot table, using the Size and Colour fields.

Then, copy or move those Slicers to the worksheet where the Excel Table is located.

Check for the ID

A named range – DD.Filter – is created, based on column A on the pivot table worksheet. In the Excel Table, a new column is added – xFilter – and a formula in that column checks for the row’s ID in the DD.Filter range.

The formula result is TRUE or FALSE, and only the TRUE rows will show after a Slicer is clicked.

Add Some Event Code

The final step is to add some event code to the pivot table, so it filters the table after a pivot table update. The pivot table update event is fired by the user action of changing a slicer selection.

Thanks for this great idea.
Unfortunately this won’t work in other than English versions of Excel:.Range.AutoFilter Field:=lCol, Criteria1:="TRUE"
For my German Excel one have to use “WAHR” instead of “TRUE”.
To have an international version you can use this one.Range.AutoFilter Field:=lCol, Criteria1:=CStr(True)
This works nice for me.

I am running into an issue where the slicers are not changing the the correct sheet, they are changing on the pivot but not on the table, what are some of the causes for this? ( all code including the VB code is in place)

@Skip
Unfortunately you gave to less information, so we have to guess!
I had the same issue and it was solved according to my posting (see above).
Would be interesting to know:
Windows Version, Regional Settings, MS Office Version, 32bit/64bit,…
Help us to help you. :-)

thanks for the reply ,
When i hit the slicer, the pivot table on the secondary page changes and the Xfilter column cont he results table goes to FALSE but, the TRUES dont filter to be shown unless i open the filter and close it and it will. It still shows all the data on the table regardless of what is showing the column.

As long as you put the VBA Code into the Pivot Table Sheet, that should solve your problem. I was having that problem, then realized the reason it was not filtering properly is because it wasn’t seeing the VBA code. So make sure you are in the pivot table sheet, click on Developer and view code, if the code is there, you shouldn’t have an issue. Just make sure all of your fields and sheet names match to the code or vice-versa. If there is no code on the pivot table sheet, then you know what to do.

I am working in one report using slicers. I looked your file alexj_TableSlicers, and i am doing something similar. What i want to do is: for example in your file when you select Colour=Blue we have data for only Large and Small, but Medium is shown in the slicer. Since i am using a slicer that it has many categories i am wondering if its possible not to show at all data in the second slicer that do not have datas (ex. in you file for size to show only Large and Small). So, to show only those categories that have data based on the selection in the “first slicer”. I hope that is clear.

I have two excel tables (non pivot) on the same worksheet and each one has its own slicer (created using the method in the article above). The tables contain a field for sales person, their region, and then fields for data over 12 months. Is there a way to have just one slicer control both of these excel table, or have one slicer mimic the other so only one is used? Thanks.

Yes, you right click on the slicer you want to control both pivot tables. Look for pivot table connections. Left click pivot table connections, it will then bring up a pop up that shows all pivot tables on that sheet. Click the box for the appropriate tables and then click ok. Now that slicer will control both pivot tables.

Nevermind, I was able to fix this. Had to make sure my table name was correct. That said, my code doesn’t seem to do anything, it runs without error but nothing results. The table does not filter when an option on the slicer is chosen. Can anyone help me with this? Thanks.

Check where your VBA code is housed in your workbook. If you look at an earlier post I responded to where I stated this: “As long as you put the VBA Code into the Pivot Table Sheet, that should solve your problem. I was having that problem, then realized the reason it was not filtering properly is because it wasn’t seeing the VBA code. So make sure you are in the pivot table sheet, click on Developer and view code, if the code is there, you shouldn’t have an issue. Just make sure all of your fields and sheet names match to the code or vice-versa. If there is no code on the pivot table sheet, then you know what to do.”

Debra, Alex & Rob. Thanks for the input. It worked awesome. I also had the same issue as Skip but the VBA code was not in my pivot sheet but under module-1, so I just put it the pivot sheet and it worked. Thanks