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.
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.
- Click any cell in the filtered range
- On the Excel Ribbon, click the Data tab
- Click the Filter button.
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.