How to create drop down menu in Excel worksheet
There are two ways how to insert drop down menu to the sheet. You can use either Data Validation – List or Form Control – Combo Box. Both have their advantages.
The following table represents a simple order form. The task is to create a drop-down menu for selecting the method of payment in the cell E3. Method can be VISA, MASTERCARD or AMERICAN EXPRESS.
Solution 1 / Data Validation – List
Data validation is used to verify or limit values, which are inserted into the cells. In this example we use Validation Criterion that allows to select some value from a list of values.
1) Select cell or cells where do you want Data validation (here it’s E3). Now choose Data Validation in the menu.
2) In the Data Validation options select Allow: List. There are two ways how to determine the source of values.
3) It’s done.
If you need to insert this drop-down menu to the other cells, just simple select, copy and paste it, like any others.
Important: If you add, change or delete some values in the source of validation list, then only drop down menu will change, not values in the cells!
Solution 2 / Form Control – Combo Box
First of all you have to see Developer tab in the Ribbon. If you don’t see it, go to the excel options and check the checkbox “Show Developer tab in the Ribbon”.
1) Write somewhere source values for Combo Box. I wrote them on the Sheet2 that can be later hidden.
5) Now just select source cells into the “Input range”. You can also select some “Cell link”, but it’s necessary only when is needed to know which item of drop down menu was chosen (for example, because of other calculations).
6) It’s done.