In addition to using Data validation, there’s a couple of other ways to create a drop down list in Excel. Both involve using the Developer tab. By default the Developer tab is not displayed. To display it:
Click the Office Button (big round button at the top left)
Click the Excel Options button at the bottom of the dialog box
Click Popular (on the left hand side)
Put a tick in Show Developer tab in the Ribbon (3rd checkbox down)
Click OK
Using the Form Controls
Click the Developer tab
Click the Insert button
Click the Combo Box tool in the Form Controls section (the second icon on the top row of the Form Controls palette)
The mouse pointer becomes a little black cross. Place your mouse where you want the top left hand corner of the drop down to be, hold the left mouse button down and drag down and across to define the size and location of the drop down.
In the screenshot below, the drop down appears to be located inside a cell, however the drop down is an object that “floats” on an imaginary layer above the spreadsheet, therefore it is not embedded inside a cell but can “straddle” across cell boundaries if desired.
To define the values for the drop-down list, right click on the drop down list and select Format Control to display the following dialog box:
The Input Range is a range of cells (from the current sheet or another sheet in this workbook) that store the values that you want to appear on the drop-down list. In the above screenshot I have selected F1:F3
The cell link is a single cell (in the current sheet or another sheet in this workbook) that stores the value that the user has selected. In the above screenshot I have selected B3. By selecting this cell, the value that the user selects is obscured by the drop-down itself, however, formulas in other cells can still reference B3.
Click OK
Note that what is stored in B3 is a value – 1,2 or 3. This value represents the item selected by the user. So if somebody selects Gold, because Gold is the 2nd item in the drop down list, the value stored in B3 is 2.
Using ActiveX Controls
Form controls have been part of Excel since version 5. Form Controls provide a way for non-technical users to add powerful features to their spreadsheets. They can have macros attached to them and don’t require any VBA coding to make them work. As you can see from this tutorial, defining the properties of a Form Control is fairly straightforward.
ActiveX controls can be used on worksheet forms, with or without the use of VBA code, and on VBA-based UserForms. In general, use ActiveX controls when you need more flexible design requirements than those provided by Form controls. ActiveX controls have extensive properties that you can use to customize their appearance, behaviour, fonts, and other characteristics.
To insert an ActiveX drop-down into a spreadsheet…
Click the Developer tab
Click the Insert button
Click the Combo Box tool in the ActiveX Controls section (the second icon on the top row of the ActiveX Controls palette)
The mouse pointer becomes a little black cross. Place your mouse where you want the top left hand corner of the drop down to be, hold the left mouse button down and drag down and across to define the size and location of the drop down.
In the screenshot below, the drop down appears to be located inside a cell, however the drop down is an object that “floats” on an imaginary layer above the spreadsheet, therefore it is not embedded inside a cell but can “straddle” across cell boundaries if desired.
To define the Properties of an ActiveX Control, ensure that the Control is selected and that the Design Mode button is enabled (it should look like the one in the screenshot above) and use the Properties button on the Ribbon.
if you need to write VBA code to run when the button is clicked, click the View Code button.