Hide Arrows in Excel AutoFilter

Hide Arrows in Excel AutoFilter

When you turn on the filter in an Excel worksheet list, or if you create a named Excel table, each cell in the heading row automatically shows a drop down arrow. If you don’t need them, here’s how you can hide arrows in Excel AutoFilter.

Excel AutoFilter Arrows

In the heading row, an AutoFilter arrow button shows a filter icon, if you have filtered that column. If you point to an arrow with a filter icon, a pop up Tool Tip shows the filter criteria.

In the screen shot below, the Customer column has been filtered to show 3 specific customers – FoodMart, MegaStore and VegiVille.

autofilterarrows01

AutoFilter Arrow Options

For an AutoFilter, you have three options for the arrow display:

  • Leave all the arrows showing
  • Remove the AutoFilter, which removes all the arrows
  • Use programming to hide one or more of the arrows.

Manually Remove the AutoFilter

For either a worksheet list, or a named table, you can manually turn the AutoFilter on and off, which also shows or hides the arrow buttons.

  1. Click any cell in the filtered range
  2. On the Excel Ribbon, click the Data tab
  3. Click the Filter button.

autofilterribbon01

Hide AutoFilter Arrows with Macro

Before Lists and Named Tables were added to Excel, there could only be one AutoFilter per worksheet.

Now, in addition to the single worksheet AutoFilter, you can put multiple named tables on a sheet. Each of those tables has its own AutoFilter property.

There are code examples below, for hiding arrows in a List AutoFilter, and a worksheet AutoFilter. There are more AutoFilter VBA examples on my Contextures website:

Hide List AutoFilter Arrows With VBA

If you want to leave one or more arrows visible, but hide the others, you can use a macro.

In this example, only the second column will have an arrow, and all the others arrows will be hidden.

This code is designed for a named table, which has its own AutoFilter property. There can be multiple named tables on a worksheet, and each table’s AutoFilter settings can be different.

Sub HideArrowsList1()
'hides all arrows except list 1 column 2
Dim Lst As ListObject
Dim c As Range
Dim i As Integer
Application.ScreenUpdating = False
Set Lst = ActiveSheet.ListObjects(1)
i = 1
For Each c In Lst.HeaderRowRange
 If i <> 2 Then
    Lst.Range.AutoFilter Field:=i, _
      VisibleDropDown:=False
 Else
     Lst.Range.AutoFilter Field:=i, _
      VisibleDropDown:=True
 End If
 i = i + 1
Next
Application.ScreenUpdating = True
End Sub

Hide Worksheet AutoFilter Arrows With VBA

To hide the arrows for a worksheet table’s AutoFilter, the code is slightly different.

There can be only one worksheet AutoFilter on a worksheet, and in this example, the filtered list starts in cell A1.

The following procedure hides the arrows for all columns except column B.

Sub HideArrows()
'hides all arrows except column 2
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False
For Each c In Range(Cells(1, 1), Cells(1, i))
 If c.Column <> 2 Then
  c.AutoFilter Field:=c.Column, _
    Visibledropdown:=False
 End If
Next
Application.ScreenUpdating = True
End Sub

Use the Hide Arrow Macros

To use these macros, copy them into a regular code module in your workbook. There are instructions here.

You would only have to run the code once, after you set up the filtered list. To run the code,

  • Click the View tab on the Excel Ribbon
  • At the far right of the tab, click the Macros command (click the picture at the top of the command)
  • In the list of macros, click the macro that you want to run
  • Click the Run button.

________________

12 thoughts on “Hide Arrows in Excel AutoFilter”

  1. I pasted this code but it does not seem to have the desired effect.
    Besides the code, is there anything else I need to do to make the autofilter *use* this subroutine?
    Seems that something, somewhere, would need to call HideArrows(), and how would Excel know to do that?

  2. Worked perfectly. i needed this for reporting purposes, as i have to paste excel tables in Powerpoint, and the arrows are unsightly. Thank you for this. Granted, i had to modify the code to hide all arrows but that was easy to do. Thank you!

  3. For the “Manually Remove the AutoFilter” you can also do this from the Home Tab (far right under “Sort & Filter”) OR what’s even easier “CTRL+Shift+L”

  4. I ran this macro, but it hid the second column and not the rest… Help!

    Sub HideArrows()
    'hides all arrows except column 2
    Dim c As Range
    Dim i As Integer
    i = Cells(1, 1).End(xlToRight).Column
    Application.ScreenUpdating = False
    For Each c In Range(Cells(1, 1), Cells(1, i))
     If c.Column  2 Then
      c.AutoFilter Field:=c.Column, _
        Visibledropdown:=False
     End If
    Next
    Application.ScreenUpdating = True
    End Sub
    1. There should be “Less Than” and “Greater Than” signs in this line:

       If c.Column  2 Then

      With those signs it will look for column numbers that are not equal to 2

       If c.Column <> 2 Then
  5. i don’t understand why simple stuff like this should require so much workarounds. select cells – remove filter. 99% of ppl in 99% of cases don’t need filters for all columns. “manual” removal is silly also coz most of the time you need a filter for at least 1 column, that;s why you uses name tables in the first place

  6. Sub HideArrowsList1()
    ‘hides all arrows except list 1 column 2
    Dim Lst As ListObject
    Dim c As Range
    Dim i As Integer
    Application.ScreenUpdating = False
    Set Lst = ActiveSheet.ListObjects(1)
    i = 1
    For Each c In Lst.HeaderRowRange
    If i 2 Then
    Lst.Range.AutoFilter Field:=i, _
    VisibleDropDown:=False
    Else
    Lst.Range.AutoFilter Field:=i, _
    VisibleDropDown:=True
    End If
    i = i + 1
    Next
    Application.ScreenUpdating = True
    End Sub

    this hides all arrows except 2nd one in table nr 1.
    how does a code look if there are multiple tables, with different cell arrows to hide?
    i managed to do it by modifying your code but had to run each one after changing “ActiveSheet.ListObjects(xxxx)” and “If i xxxx Then”
    how about if for table nr xx i need more than one column to show arrows?

  7. I tried the macro under “Hide Worksheet AutoFilter Arrows With VBA”. It hides the arrows but defeats the filter. In other words, if the filter has displayed a limited number of rows, running this macro redisplays all the rows.

    So, is it possible to hide the dropdown arrows but retain only the filtered rows?

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.