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.
Example
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.
A) Write values directly in the source field.
B) Or select the cell range, that contains values. In Excel 2007 and lower the source cells must be on the same sheet as the drop down menu.
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.
2) Go to the Developer tab and select Combo Box from Form Controls.
3) Draw Combo Box into the sheet.
4) Do right click on the combo box and choose “Format Control…”.
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.
How to insert select box in Excel worksheet.
can you help in Paris Texas
How can I make a drop down menu dependent on the cell before it. Example if the cell c5 chooses a certain name from the drop down menu in cell C5, then the choices in the drop down menu on D5 is different than if C5 had chosen a different item from the drop down menu.
So you need dependent Drop-down Lists in Excel? You will have to improve the source table. You can use IF functions and some temporary table to decide which data will be used in each drop down.
I have similar problem. I need 3 selects. How can I create dependent select boxes in Excel. Can you be more specific? Thank you.
can you tell me how do you get to keep the blank part which is right above visa? I hope you know what I mean. Thank you
I think you have to put the blank cell into the source values (Step1 in Example 2).