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.

Simple order form

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.
Data Validation ribbon
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.
Data Validation criteria list
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.

Data Validation criteria cells source

3) It’s done.

Data validation list of values

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.

Source for combo box

2) Go to the Developer tab and select Combo Box from Form Controls.
Developer menu Combo Box
3) Draw Combo Box into the sheet.
drawing combo box
4) Do right click on the combo box and choose “Format Control…”.

Right click on the combo box

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).
Combobox Format Object
6) It’s done.
Combo Box

Add a comment »3 comments to this article

  1. can you help in Paris Texas

    Reply

  2. 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.

    Reply

  3. 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

    Reply



Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress