How to get rid of drop down arrows in Excel table

As with the wide variety of drop-down menus, Excel's have arrows you can click. However, for some people it can be a bit annoying not knowing how remove or hide these arrows then when importing or exporting Excel documents. This drop-down menu is very useful as it provides a wide variety of tools you can use, such as words, phrases, numbers or arrows.

All of them they can be from different originsYou can enter them manually, which originate from the type of cells the spreadsheet has or the values ​​we previously entered into a list. As you can see, it is a topic that is worth knowing, so in this information we have prepared a number of points related to this subject that may be of interest to you will be discussed.

What are the main functions of drop-down arrows in Excel tables?

You must know they exist three types of drop arrows that you can create. Two of them are intended to enter the information you have selected into a cell, and the last is where we enter a number into a cell that is related to the designated information, remaining clearly displayed on the control. Next, we'll explain more clearly the function of each of these drop-down menus:

• Drop-down arrows that are automatically created from the context menu, which we can create by right-clicking and choosing the 'Choose from the dropdown list…' option. It has limited usefulness, however, it is taken very quickly and spontaneously from the list that has been inserted into the cell.
• Custom drop-down arrows created from 'data validation' command located in the 'Data' section of 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 through 'Copy and paste' or through 'Auto-Fill'.
• Drop arrows created by of a control form known as 'Mixed Tables', located in the 'Insert' section found under the 'Controls' set in the 'Programmer' section. The main feature that differentiates it from the others is that it is not stored in a cell, but in a series of item layouts, as well as in the cell that we want it to be stored.

How should a pivot table be set up in Excel to improve the experience?

Taking into consideration all the aspects that can be improved in 'a PivotTable in Excel' where you can use the PivotTable wizard to help you make them more attractive, we will show you how you can get it in a simple way and easy way:

• The first is, select one any area of ​​the sheet calculation, which will expose the pivot table, you should select 'Design' and click on the 'Report Design' section.
• tighten type of format you prefer To use this can be 'Show in Compact Format', this way the data will not be spread horizontally outside the screen. If you want it to have a classic format scheme, select 'Show in Tabular Format'; if you want to give the data a traditional style, select 'Show layout format'.
• To modify the view of labels reopen the pivot table and press 'Options' or 'Analyze'. Choose 'Active Field' option then you should enter 'Field Setup'.
• Once, click on 'Design and Print' and then the 'Design' section. Different options will be shown, select 'Show element labels in schematic format' so that are exposed to view said elements.
• For the labels to be displayed in a compact format Click 'Show item labels in schematic format' and then 'Show items from next field in same column'. Finally, press 'Show element labels in tabular format' so that they are displayed on the screen.
• Enable or disable headers Entering the table's tool strip, select the 'Options' section, in the 'Show' area we choose 'Field Header'.

• For I know show subtotals In the part below the top of the line, open the tool table and enter the section 'Options', 'Active field' and 'Field configuration'.
• Go to 'Field Settings', 'Subtotals and Filter', select the 'Subtotals' option, choose between 'Custom or Automatic'.

• Then go to 'Drawings 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 in three different ways two of which mainly depend on which operating system you use, which can be Windows or Mac, the latter method can be done by through your website.

On windows

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

• The first thing is to choose the cell where the drop-down arrow is located. If you have a series of dropdown arrows that you want to eliminate, 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 click on the 'OK' button.

If you need disable any type of 'Validation of data' and even the drop-down arrows of which you don't know the location, you can do this through the dialog 'Go', 'Special' and mark the command Ctrl+G, special, go to 'Data validation' and choose between 'All or Equal' and repeat the steps we performed earlier.

from the mac

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

Using the website

Choose the cell containing the arrow and proceed to enter the section 'Data', 'Data validation', 'Configuration', 'Delete all' and finally press 'OK', the drop-down arrow will be eliminated.

What can I do to discard all the dropdown arrows in my document?

You can do this using the command Ctrl l + G to mark all arrows if you have the Windows operating system, but if you have a Mac, you must put the command Cmd + Left Button. In both cases after performing the command go to 'Data', there you should go to 'Data validation' then go to 'Configuration' and with that proceed to choose the option 'Delete all' and click on 'OK'

How to Suppress Dropped Arrows Using MACROS

With this method, we get several benefits, names remain intact from and you can choose whether you want to remove one arrow from the drop-down menu or all of them. On the surface, this procedure may seem a little complicated, but in reality it's basically very careful copying and pasting.

discard everything

To do so, follow the instructions we will give you below, it is recommended that before doing so examine a piece of code You must do to discard all arrows in the Excel document.

• Sub DisableSelection() Dim pt as pivot table Dim pt as pivot field Set pt = ActiveSheet.PivotTables (1) For each pf to pt.PivotFields pf.EnableItemSelection = False Next pf Finish sub. This code will be applied in all fields and cells, will also disable the element choice function. This means it will disable 'all arrows in pivot table'.
• The next step will be to copy the following command that may vary from on a Mac like Cmd + C or on a PC with Windows system to Ctrl + C. The next thing is to go to 'Developer' found in the Excel program toolbar and choose 'Visual Basic' from the menu. Some versions of Excel don't have the option 'Developer'. You can use the command Alt+F11 and access the Visual Basic menu.
• Choose the project you are working on the Visual Basic menu at the top right corner. Click your mouse on the 'Insert' option found in the tool strip and go to 'Module'.

• Go back to the Excel spreadsheet and choose any cell. Choose the 'View' option, enter the MACROS menu on the far right and proceed to place the code you just pasted and click on 'Run', like that and all the arrows will be removed from the table.

just the one from a list

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

• Sub DisableSelectionSelPF() Dim en As PivotTable Dim pf As PivotField. If an error occurs, use the following: Set pt = ActiveSheet.PivotTables (1) Set pf = pt.PageFields (1) pf.EnableItemSelection = False end sub. With that, it will only be enough for us to repeat steps 2 to 4 that we did before.

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