How to Remove the Dropdown Arrow in Excel

Like most other drop-down menus, the ones in Excel feature clickable arrows. However, you might want to hide or remove the arrows when you export or share your Excel files.

So how do you remove the unwanted arrows? There are two methods to do it – one is quite easy and utilizes basic Excel tools and the other requires you to apply a specific code to the file you are working on. Either way, the following guide should help you do it without breaking a sweat.

Pivot Table Settings

This is the quick and easy method, but you should know that the action also hides the field names. If you don’t mind that, feel free to check out the steps below. Otherwise, jump right into the more advanced coding/macros method.

Step 1

Select the first cell under the field name and right-click on it. Click on PivotTable Options in the pop-up menu, you should find it at the bottom of the list.

Step 2

Once the PivotTable Options window appears, you should select the Display tab. You are looking for “Display field captions and filter dropdowns.” This feature is checked by default and you need to uncheck it in order to make the arrows disappear.

When you uncheck the feature, click OK at the bottom of the window for the changes to take effect. Preview the table to determine if everything looks okay without the field names.

Macros Method

The benefits of this method are that the field names stay intact and you can choose to remove all drop-down arrows or just one of them. On the surface, this method might look tricky but it mostly boils down to careful copying and pasting.

Removing All the Arrows

Step 1

First, check out the piece of code you need to implement to get rid of all the arrows in your file.

Sub DisableSelection ()

'remove the dropdown arrow tutorial by techjunkie.com

Dim pt As PivotTable

Dim pt As PivotField

Set pt = ActiveSheet.PivotTables (1)

    For Each pf In pt.PivotFields

           pf.EnableItemSelection = False

    Next pf

End Sub

This code goes through all the fields and cells and disables the Item Selection feature. In other words, it disables all the arrows within the pivot table.

Step 2

Copy the entire code/macro – use Cmd+C on a Mac or Ctrl+C on a Windows computer. Mind you, the code should be copied as-is because even a minor typo may affect its functionality.

Now, you need to click on the Developer tab under the Excel toolbar and select the Visual Basic menu. It should be the first option in the Developer menu.

Note: Some Excel versions might not feature the Developer tab. If you encounter this problem, use the Alt+F11 keyboard shortcut to get right into the Visual Basic menu.

Step 3

Select the workbook/project you are working on from the menu in the upper left of the Visual Basic window. Click on Insert in the toolbar and select Module.

The module should appear in a large menu on the right and your cursor should be just where you need to paste the code. When you paste the code, the comment line (the one that starts with the apostrophe) turns green and the other lines are black and blue.

Step 4

Go back to your Excel sheet and select any cell. Select the View tab, click on the Macros menu on the far right and then choose the macro/code you’ve just pasted in.

It should be the first one on the menu. Select it, click Run, and all the arrows will disappear from the table.

Removing One Arrow

Again, this is the code that you can use to remove just one of the drop-down arrows.

Sub DisableSelectionSelPF ()

'remove the dropdown arrow tutorial by techjunkie.com

Dim pt As PivotTable

Dim pf As PivotField

On Error Resume Next

Set pt = ActiveSheet.PivotTables (1)

Set pf = pt.PageFields (1)

       pf.EnableItemSelection = False

End Sub

From here on, you should follow the steps 2 to 4 from the previous section.

Note: For the purposes of this article, the macro is programmed to get rid of the first arrow that it encounters. The code might be a bit different if you want to remove another arrow.

Thing to Consider

The methods have been tried and tested on a small sheet that contains 14 rows and 5 columns. Nevertheless, they should also work on much larger sheets.

It is worth noting that the steps apply to Excel versions from 2013 to 2016. The macros should also apply to newer software iterations but the tool layout might be a bit different.

When using macros, you can revert the changes by changing the value from = False to = True. Put a few blank lines in the module, paste the entire code and just change the pf.EnableItemSelection line.

Shoot the Invisible Arrow

Using macros is often considered intermediate or even advanced Excel knowledge. In reality, the macros are not that hard to master and can help you quickly get rid of the arrows and do many other cool things.

Why would you like to remove the arrows from your sheet? Have you used macros before? Share your experience with the rest of the TechJunkie community.