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. 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.

_____________