Excel: How to hide/remove scrollbars from Pivot Table Slicers

microsoft excelmicrosoft-excel-2010pivot tablevba

Slicers are a fantastic tool new to Excel from 2010, allowing you to quickly switch your selections when analysing data using a pivot table.

Sometimes there are too many options to fit in the slicer window so you get a vertical scrollbar (ignore the numbers in this image):

slicer showing scrollbar

Often, I will have a pivot table with a static report filter selection – ie a filter that I do not want the user to change, eg Year = 2013:

pivot table report filter

This selection then means certain values in my slicer are irrelevant (using the example above, perhaps a salesperson called Smith left before 2013) so I don't want to show them in the slicer (but I do want to retain them in my pivot table data source).

What I'd like to do is suppress the vertical scrollbar in the slicer so that the irrelevant options are not presented to the user. Can this be done, either by a slicer setting or VBA?

At the moment my rather inelegant solution is to overlay a white rectangle… the downside is the 'clear filter' button is then awkwardly pushed out to the top right. If there's a better way to remove the scrollbar (or redundant option) I'd love to hear it!

Best Answer

It would appear that this cannot be done. Therefore the best solution would seem to be the one I'd already gone for (mentioned in the question):

my rather inelegant solution is to overlay a white rectangle... the downside is the 'clear filter' button is then awkwardly pushed out to the top right.

Thanks to Jan Karel Pieterse on LinkedIn for this confirmation.

Related Solutions

Excel – How do you format the date filter selection box for Excel Pivot Table

Nope. (Is this even an acceptable answer?)

You'll find that some "shared" functionalities between Tables and PivotTables aren't always identical - Custom Filters is another one.

But there's an alternative: you can create a few extra columns in your source records table to deal with that. You just have to fill them with a simple formula based on your date cells. That would be Year:

=YEAR(Date)

and Month,to your liking:

=MONTH(Date)                 ' shows 1 (January)
=TEXT(MONTH(Date), "mmm")    ' shows "Jan"
=TEXT(MONTH(Date), "mmmm")   ' shows "January"

You can then use those 2 fields in the "Report Filter" area of your PivotTable as a handy and quick access for filtering your dates.

Another huge benefit of adding these fields is that you can also use them for breaking down your PivotTables by year or month without having to mess with pivot groupings.

Excel – Add Slicer Selections to sheet in a list – Excel VBA

Here's a User Defined Function that you can call directly from the workbook that does this for you, and can be run on any type of Slicer, be it a 'traditional' PivotTable, an OLAP/PowerPivot PivotTable, or a Table Slicer. Just put this in a standard code module, and then in the workbook type the following:

=SlicerItems("Slicer_City")

Public Function SlicerItems(SlicerName As String, Optional sDelimiter As String = "|") As String

    Dim oSc As SlicerCache
    Dim oSi As SlicerItem
    Dim i As Long
    Dim lVisible As Long
    Dim sVisible() As String

    On Error Resume Next
    Application.Volatile
    Set oSc = ThisWorkbook.SlicerCaches(SlicerName)
    If Not oSc Is Nothing Then
        With oSc
            If .FilterCleared Then
                SlicerItems = "(All)"
            Else
                If .OLAP Then
                    SlicerItems = Join(.VisibleSlicerItemsList, sDelimiter)
                    SlicerItems = Replace(SlicerItems, .SourceName, "")
                    SlicerItems = Replace(SlicerItems, ".&[", "")
                    SlicerItems = Replace(SlicerItems, "]", "")
                Else

                    lVisible = .VisibleSlicerItems.Count
                    If .VisibleSlicerItems.Count = 1 Then
                        SlicerItems = .VisibleSlicerItems(1).Name
                    Else
                        ReDim sVisible(1 To lVisible)
                        For i = 1 To lVisible
                            sVisible(i) = .VisibleSlicerItems(i).Name
                        Next i
                        SlicerItems = Join(sVisible, sDelimiter)
                    End If
                End If
            End If
        End With
    Else
        SlicerItems = SlicerName & " not found!"
    End If

End Function

And here's how it looks:

Related Question