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. You couldn’t use Slicers to filter a table in Excel 2010 though.
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.
Here is the code from the SalesPivot worksheet module.
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim tbl As ListObject
Dim lCol As Long
Application.ScreenUpdating = False
Set tbl = Worksheets("SalesData").ListObjects("Table1")
lCol = tbl.ListColumns("xFilter").Index
With tbl
If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
.Range.AutoFilter Field:=lCol, Criteria1:="TRUE"
End With
Application.ScreenUpdating = True
Set tbl = Nothing
End Sub
Download the Sample File
To download the AlexJ’s sample file, you can visit his page on the Contextures website. In the Filters section, look for
FL0002 – Filter Excel 2010 Table With Slicers
The file is designed for Excel 2010 only, and you’ll have to enable macros to test the file.
_____________
Any update on this ? Thanks in advance
Skip – can you upload a sample file to a filesharing site like dropbox, and post the link here?
What was resolution to skip’s problem? I am having same problem. The debug is highlighting the first row of event code.
The only thing I changed besides source data was the name of the tab. I changed “salesdata” to “issue_log”
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.
Thanks Rob. That tip worked for me.
Hi,
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.
Thanks for any help.
Regards,
ShpendG
Skip, make sure you are putting the VBA Code into the Pivot Table Sheet, that should solve your problem.
Hi Debra,
This is fun, smart, …! Thanks.
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.
Dang, sorry Luis, I misread your question. Ignore my reply!