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.

slicerstableaj01

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.

slicerstableaj02

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)

slicerstableaj03

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.

slicerstableaj04

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.

slicerstableaj05

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.

_____________

You may also like...

27 Responses

  1. Tashfeen Hasan says:

    In pivote report I can utilized also Report Filter cell for other connection, but…! if I using Slicer how I utilized Filtered item for other connection?

  2. Hugo says:

    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.

  3. Skip says:

    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)

  4. Hugo says:

    @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. :-)

  5. Jeff Weir says:

    Alex, this is an awesome idea. Thanks for sharing.

  6. Skip says:

    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.

  7. Skip says:

    Any update on this ? Thanks in advance

  8. Jeff Weir says:

    Skip – can you upload a sample file to a filesharing site like dropbox, and post the link here?

    • Rob Byron says:

      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”

      • Rob says:

        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.

  9. ShpendG says:

    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

  10. Rob says:

    Skip, make sure you are putting the VBA Code into the Pivot Table Sheet, that should solve your problem.

  11. Hi Debra,
    This is fun, smart, …! Thanks.

  12. Luis says:

    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.

    • Rob says:

      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.

  13. Beth says:

    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?

    • Ethan says:

      I’m getting this same error. Can you tell me what you did to fix it please?

    • Ethan says:

      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.

      • Rob says:

        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.

  14. Beth says:

    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?

  15. Lyn says:

    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?

  16. Viggy says:

    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

  17. Tom says:

    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

  18. Crystal says:

    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.

  19. Ritasha Amrithlall says:

    Hi there

    I am using a pivot table that shows me resources that have submitted timesheets, not submitted or rejected timesheets over a 4 week period.
    eg

    Week1 week2 week3 week4
    Resource1 Sub Sub Sub Sub
    Resource2 Sub Sub Sub Rej
    Resource3 Sub Sub Not Rej

    So if use a slicer and filer on NOT, then it displays like below
    Week1 week2 week3 week4
    Resource3 Not

    How can I get the report to still show week1 2 and 4 status?

    Thanks
    R

Leave a Reply to Tom Cancel reply

Your email address will not be published. Required fields are marked *