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.
_____________
I am getting the error “Subscript out of range” on the line of the code
Set tbl = Worksheets(“SCCM and AD Match”).ListObjects(“Table1”)
whenever I select anything on the slicer.
That is the correct name for the sheet with the data and that is the correct named range for the data.
Also, how did you manage to have the pivot table work when your named range doesn’t include the column headings?
I’m getting this same error. Can you tell me what you did to fix it please?
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.”
You should have your table filter properly.
Sorry. I figured out my previous error.
Is it possible to get the data sheet to automatically refresh to reflect the filter of “TRUE” for the xFilter column?
HI There,
I have a problem with the slice.
It is showing all the available in the other field even after i have applied a filter.
Can you advise?
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
Trying to find a solution to the view or what displayed in the slicer tab. In excel 2010 when i format the slicer it will display 10 names based on the piviot field selected.
Is there a way to remove or hide 5 of these names and only show 5 of the 10 original items in the slicer
Your help wpuld be appreciated
I am able to re-create all – but my excel table – does not take the xFilter – it is not making it turn to TRUE – all are remaining FALSE. Any idea of what I am doing wrong?
I have inserted the code in the pivot sheet – and even created the exact same example.