A more recent version of the Excel course is available here: Excel Course

Excel Course: Drop-Down List

To insert a drop-down list you must have the "Developer" tab.

If it is not shown, click on "File", "Options", "Customize Ribbon" tab and check "Developer".

customize ribbon tab excel dropdown list

The tab should now be displayed.

developer tab excel dropdown list

Drop-down List

Click on "Insert" and choose "Combo Box".

insert dropdown list excel

Select your drop-down list and click on "Format Control".

format dropdown list excel

In the "Input range" field, enter the range of cells that contain the values you want in your drop-down list (in this case, cities).

In the "Cell link" field, enter the cell where the result should be displayed (the value chosen from the list).

In the "Drop down lines" field, enter the number of values from the list that you want to be visible at once.

format dropdown list2 excel list

Your drop-down list is now ready.

display dropdown list excel

The drop-down list returns a number in cell E6.

How to Display a Text Value Based on the Number Returned

When you select New York, for example, cell E6 will show the value 5 (which is its position in the drop-down list, therefore its position in the table).

To display the text value that is associated with this number, use the INDEX function.

In the "Array" field, enter the cell range and in the "Row_num", the cell that contains the position number (this should be the same information entered in "Format Control").

dropdown list value excel

The results:

dropdown list values excel