Dropdown list in a cell

Video

 Who has little time and needs to quickly grasp the essence – watch the training video:

Who are interested in the details and nuances of all the methods described – further down the text.

Method 1. Primitive

Single right-click on an empty cell under a column with data, context menu command Select from drop down list (Choose from drop-down list) or press the keyboard shortcut ALT+down arrow. The method does not work if at least one empty line separates the cell and the data column, or if you need a product that has never been entered above:

Method 2. Standard

1. Select the cells with the data that should be included in the drop-down list (for example, product names).
2. If you have Excel 2003 or older, select from the menu Insert – Name – Assign (Insert — Name — Define), if Excel 2007 or newer, open the tab Formulas and use the button Name ManagerThen Create. Enter a name (any name is possible, but without spaces and start with a letter!) for the selected range (for example Product). Click on OK.
3. Select the cells (you can have several at once) in which you want to get a drop-down list and select from the menu (on the tab) Data – Check (Data – Validation). From drop down list Data Type (Allow) select option List and enter in the line Source equals sign and range name (i.e. =Products).

Press OK.

Everything! Enjoy!

An important nuance. A dynamic named range, such as a price list, can also serve as a data source for a list. Then, when adding new products to the price list, they will be automatically added to the drop-down list. Another commonly used trick for such lists is to create linked dropdowns (where the content of one list changes depending on the selection in another).

Method 3: Control

This method is to insert a new object on the sheet – a combo box control, and then bind it to the ranges on the sheet. For this:

1. In Excel 2007/2010, open the tab Developer. In earlier versions, the toolbar Forms through the menu View – Toolbars – Forms (View – Toolbars – Forms). If this tab is not visible, then click the button Office – Excel Options — checkbox Show Developer Tab in the Ribbon (Office Button – Excel Options – Show Developer Tab in the Ribbon)
2. Look for the dropdown icon among form controls (not ActiveX!). Follow the pop-up hints combo box:

   Click on the icon and draw a small horizontal rectangle – the future list.

3. Right-click on the drawn list and select command Object Format (Format control). In the dialog box that appears, set

• Form a list by range – select the cells with the names of the goods that should be included in the list
• Cell communication – specify the cell where you want to display the serial number of the element selected by the user.
• Number of list lines — how many rows to show in the dropdown list. The default is 8, but more is possible, which the previous method does not allow.

After clicking on OK list can be used.

To display its name instead of the serial number of the element, you can additionally use the function INDEX (INDEX), which can display the contents of the required cell from the range:

Method 4: ActiveX control

This method partially resembles the previous one. The main difference is that it is not a control that is added to the sheet, but an ActiveX control. “Combo Box” from the dropdown box below the button Insert from the tab Developer:

The adding mechanism is the same – select an object from the list and draw it on the sheet. But then serious differences from the previous method begin.

Firstly, the created ActiveX drop-down list can be in two fundamentally different states – debug mode, when you can configure its parameters and properties, move it around the sheet and resize it, and – input mode, when the only thing you can do is select data from it. Switching between these modes is done using the button. Design Mode tab Developer:

If this button is pressed, then we can adjust the parameters of the drop-down list by pressing the adjacent button Properties, which will open a window with a list of all possible settings for the selected object:

The most necessary and useful properties that can and should be configured:

• ListFillRange – the range of cells where the data for the list is taken from. It will not allow you to select a range with the mouse, you just need to enter it with your hands from the keyboard (for example, Sheet2! A1: A5)
• LinkedCell – the associated cell where the item selected from the list will be displayed
• ListRows – number of displayed rows
• Font – font, size, style (italic, underline, etc. except for color)
• ForeColor и backcolor – text and background color, respectively

A big and fat plus of this method is the ability to quickly jump to the desired element in the list when entering the first letters from the keyboard(!), which is not available for all other methods. A nice point, also, is the ability to customize the visual presentation (colors, fonts, etc.)

When using this method, it is also possible to specify as ListFillRange not only one-dimensional ranges. You can, for example, set a range of two columns and several rows, indicating additionally that you need to display two columns (property ColumnCount=2). Then you can get very attractive results that pay off all the effort spent on additional settings:

Final comparison table of all methods

:

• Dropdown list with data from another file
• Creating Dependent Dropdowns
• Automatic creation of dropdown lists by the PLEX add-on
• Selecting a photo from the dropdown list
• Automatic removal of already used items from the dropdown list
• Dropdown list with automatic addition of new items