How to remove the dropdown arrow from my Excel – Set up your tables

by

As in the great variety of drop-down menus, those in Excel have arrows that you can click on. However, some people may find it a bit annoying not knowing how to remove or hide these arrows later when importing or exporting Excel documents. This dropdown menu is very useful, as it provides you with a wide variety of tools that you can use, such as words, phrases, numbers or arrows.

How to remove the drop down arrow from my EXCEL – Set up your tables

All of them can be of different origin , you can enter them manually, that they originate from the class of cells that the spreadsheet has or from the values ​​that we entered previously in a list. As you can see, it is a subject that is worth knowing, for that reason in the present information that we have prepared, a certain number of points related to this issue that may be of interest to you will be discussed.

Index( )

  1. What are the main functions of dropdown arrows in Excel tables?
  2. How should you configure a pivot table in Excel to improve the experience?
  3. How do I remove the dropdown arrow from my list in Excel?
    1. on Windows
    2. From Mac
    3. Using the website
  4. What can I do to get rid of all the dropdown arrows in my document?
  5. How to suppress drop down arrows using MACROS
    1. discard all
    2. Just the one from a list

What are the main functions of dropdown arrows in Excel tables?

You should know that there are three types of dropdown arrows that you can create. Two of them are aimed at entering a cell with the information you have selected, and the last one is in which we enter a number in a cell that is related to the designated information, remaining clearly presented in the control. Next, we will explain more clearly the function of each of these drop-down menus:

  • Dropdown arrows that are created automatically from the contextual menu, which we can create by right-clicking the mouse and choosing the option ‘Choose from the dropdown list…’. It has a limited utility, however, they are obtained very quickly and spontaneously from the list that has been entered in the cell.
  • Drop-down arrows created in a personalized way, from the ‘Data validation’ commandlocated in the ‘Data’ section in the Excel menu bar. It is one of the most versatile to use to create them, placing information in the cell, which you can even replicate by means of ‘Copy and paste’ or by means of ‘Auto-fill’.
  • Dropdown arrows created by means of a control formknown as ‘Combo Boxes’, located in the ‘Insert’ section found in the ‘Controls’ set in the ‘Developer’ section. The main characteristic that differentiates it from the others is that it does not remain stored in a cell, but rather it is stored in a number of items available, as well as the cell that we want it to be stored.

How should you configure a pivot table in Excel to improve the experience?

Taking into account all the aspects that can be improved in ‘a pivot table in Excel’ in which you can use thepivot table wizard to  Help you make them more striking, we will show you how you can achieve it in a simple and easy way:

  • The first is to select any area of ​​thespreadsheet, which will expose the dynamic table, there you must select ‘Design’ and click on the ‘Report design’ section.

 

 

  • Press the type of format you prefer touse, it can be ‘Show in compact format’, with this the data will not be spread horizontally outside the screen. If you want it to have a classic format schema select ‘Show in tabular format’, if you want to give the data a traditional style select ‘Show schema format’.
  • To modify the view of the labelsopen the pivot table again and press ‘Options’ or ‘Analyze’. Choose the option ‘Active field’ then you must enter ‘Field configuration’.
  • Once click on ‘Design and printing’ and then on the ‘Design’ section. You will be shown different options, select ‘Show labels of elements in schematic format’ so that these elements are exposed to view .

 

 

  • To display the labels in a compact format,click ‘Show element labels in schematic format’ and then ‘Show next field elements in the same column’. To finish, press ‘Show element labels in tabular format’ to display them on the screen.
  • Activate or deactivate headersby entering the table’s tool ribbon, select the ‘Options’ section, in the ‘Show’ area we choose ‘Field header’.
  • To show the subtotalsin the lower part of the rows, open the tools table and enter the ‘Options’, ‘Active field’ and ‘Field configuration’ section.
  • Go to ‘Field Settings’, ‘Subtotals and Filter’, select the ‘Subtotals’ option, choose between ‘Custom or Automatic’.
  • Then go to ‘Designs and printing’, choose ‘Design’ and then ‘Show element labels in schematic formats’. Activate the option ‘Show subtotals at the top of each group’ or to deactivate them press ‘Show subtotals at the top of each group’.

How do I remove the dropdown arrow from my list in Excel?

You can do it in three different ways , two of which depend mainly on which operating system you use, which can be Windows or Mac, the last method can be donethrough their website.

and windows

If you want to remove the drop-down arrow found in your Excel file in a simple and uncomplicated way, follow the instructions that we will provide below:

  • The first thing will be to choose in the cell in which the drop-down arrow is located. If you have a series of drop-down arrows that you want to remove, you can use the ‘Ctrl + Left Button’ command to mark them all.
  • The second step to follow is to click on ‘Data’ and then on ‘Data validation’. In ‘Settings’ you must check the ‘Delete all’ option and finally press the ‘OK’ button.

If you need to disable any kind of ‘Data Validation ‘ and even dropdown arrows for which you don’t know their location, you can do so by going to the ‘Go’ dialog, ‘Special’ and checking the command Ctrl + G, special, go to ‘Data validation’ and choose between ‘All or Same’ and repeat the steps we gave previously.

From Mac

  • Choose the cell where the dropdown arrow is located, use the command ‘Cmd + Left Button’ if you want to remove multiple dropdown arrows so that they are selected.
  • The second thing is to enter ‘Data’ and then ‘Data validation’. In ‘Settings’ we click on ‘Delete all’, finally, we press the ‘OK’ button.

Using the website

Choose the cell that contains the arrow and proceed to enter the ‘Data’ section, ‘Data validation’, ‘Settings’, ‘Delete all’ and finally press ‘OK’, with that the drop-down arrow will be eliminated.

What can I do to get rid of all the dropdown arrows in my document?

You can do this using the Ctrl l + G command to mark all the arrows if you have the Windows operating system, but if you have a Mac you must place the command Cmd + Left Button. In both cases, after carrying out the command, go to ‘Data’, there you must go to ‘Data validation’ then go to ‘Settings’ and with this proceed to choose the ‘Delete all’ option and click ‘OK’

How to suppress drop down arrows using MACROS

With this method we get several benefits, the names remain intact and you can choose if you want to remove one arrow from the dropdown menu or all. On the surface, this procedure may look a bit complicated, but in reality it is basically a very careful copy and paste.

discard all

To do this, follow the instructions that we will give you below. It is recommended that before doing so, you examine a part of the code that must be carried out to discard all the arrows in the Excel document.

  • Sub DisableSelection() Dim pt As PivotTable Dim pt As PivotField Set pt = ActiveSheet.PivotTables(1) For each pf to pt.PivotFields pf.EnableItemSelection = False Next pf End sub. This code will be applied to all fields and cells, plus it will disable the item picking feature. What it means is that it will disable ‘all arrows in the pivot table’.
  • The following will be to copy the following command which can varyfrom on a Mac like Cmd + C or on a PC with a Windows system to Ctrl + C. The next thing is to enter ‘Developer’ found in the Excel program tool ribbon and choose ‘Visual Basic’ from the menu. Some versions of Excel do not have a ‘Developer’ option. You can use the command Alt + F11 and you will access the Visual Basic menu.
  • Choose the project you are working on from the Visual Basic menuat the top right. Click with the mouse on the ‘Insert’ option found on the tools ribbon and enter ‘Module’.
  • Go back to the Excel sheet and choose any cell. Choose the ‘View’ option, enter the MACROS menu that is on the far right and proceed to place the code you just pasted and click on ‘Execute’, in this way all the arrows will be eliminated from the table.

Just the one from a list

In the same way we will use the same code again, with it we can not only remove all the arrows, but we can also use it to remove a single arrow from the document.

  • Sub DisableSelectionSelPF () Dim pt As PivotTable Dim pf As PivotField. If any error occurs proceed to use the following: Set pt = ActiveSheet.PivotTables (1) Set pf = pt.PageFields (1) pf.EnableItemSelection = False end sub. With this, it will only be enough to repeat the steps from 2 to 4 that we carried out previously.

Something that you should keep in mind is that the processes have been carried out with a small size sheet that has 14 rows and 5 columns. from 2013 to 2016.

 

Related Posts: