Creating a slicer for pivot table to select multiple values

Question

I have a dataset from excel which I created a pivot table for like below:

So I created a pivot table based on the data to sum the Check per Name from the raw data. I want to use Time as a slicer here - but as you can see, if I want to select the last 12 weeks of data, I will need to filter to both Last 4 Weeks and 5-12 Weeks. If I want to select all the time ranges, I will need to check all the items within the slicer.

Just wondering if there is a way for me to create a slicer, with options:

Last 4 Weeks (If I click this I will filter my data with Time = Last 4 Weeks
Last 12 Weeks(If I click this I will filter my data with Time = Last 4 Weeks and Time = 5-12 Weeks)
All (If I click this I will select every time value - basically a select all.)

Any help would be appreciated. Thanks so much!!

Алексей Р Алексей Р · Accepted Answer · 2021-02-27T06:28:07

Another way is to create your own "slicer" of shapes and assign macros to each shape (using a smart table as an example):

Macros must be placed in the sheet module.

Sub w4()
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:= _
        "=Last 4 Weeks"
End Sub

Sub w5()
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:= _
        "=5-12 Weeks"
End Sub

Sub w12()
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:= _
        "=5-12 Weeks", Operator:=xlOr, Criteria2:="=Last 4 Weeks"
End Sub

Sub w13()
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:= _
        "=13-20 Weeks"
End Sub

Sub FReset()
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2
End Sub

Creating a slicer for pivot table to select multiple values

2 Answers