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.
________________
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?
@William, I’ve added instructions at the end of the article, on how to run the macros.
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!
Thank you this was really helpful and a fun little code.
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”
So simple. Thank you a million times!
I ran this macro, but it hid the second column and not the rest… Help!
There should be “Less Than” and “Greater Than” signs in this line:
With those signs it will look for column numbers that are not equal to 2
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
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?
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?