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.

________________