Drop-down list in Excel

Create a Drop-Down List

drop-down list also known as a drop-down menu is a graphical control object that offers a list of options. In cells with a drop-down list, the user need not type in anything but can make the selection from the available options.

Drop-down list in Excel

In this blog post you will see, How to set up a Drop-down list using the Data Validation feature in Excel.

Following is a list of Indian desserts (cells from B3 to B6).

To create a drop-down list of the above desserts in a cell or cells,

Select the cell or cells where you want the drop-down list. In the Data tab of Excel Ribbon > Data Tools > Data Validation

Data Validation in Excel

In the dialog called Data Validation > Settings > Select ‘List’ from the options under the heading Validation criteria

Data Validation dialog

In the Input box under the heading ‘Source’, either type in the address of the cells containing the source data or select those cells using the mouse.

Source for drop-down list

In this case, B3:B6 is the address of the cells containing source data. $ is added before the Row and Column indexes to make the references ‘Absolute’.

Source for drop-down list

Click OK and you will have a drop-down list in the selected cell/cells. A cell with a drop-down list, when selected will display a small down arrow on its right side.

Drop-Down menu in Excel

Drop-down list of that cell can be activated by clicking this arrow or the keyboard shortcut, Alt + ↓

Drop-down list in Excel

Drop-down list can be copied to other cells using the Excel ‘fill handle’ or the Copy-Paste option.

Also, if you try to make an entry that is not listed in the drop-down list, Excel will display a warning message.

Data Validation Error

To allow values other than those listed in the drop-down list,

Activate the Data Validation dialog > In the Error Alert tab, unmark the checkbox against the label, ‘Show error alert after invalid data is entered’.

Remove Data Validation Error

Drop-down lists with manually entered data

Drop-down lists can also be created using manually entered data. Suppose you want a drop-down list with only two options Yes and No

  • Select the cell or cells where you want the drop-down list and activate the dialog for Data Validation from the Data tab.
  • In the dialog called Data Validation > Settings tab > Select ‘List’ from the options under the heading Validation criteria
  • In the Input box under the heading ‘Source’, type in ‘Yes, No
Data Validation in Excel
  • Click OK and we will have a drop-down list with two options ‘Yes’ and ‘No’ in the selected cell/cells.
Yes or No in Drop-Down List

Dynamic drop-down list

The drop-down lists which we created in the above examples are of static nature. If you want to add data to the lists like these, you should update the source data as well as the address of the source specified in the Validation criteria.

There are a few ways to make a drop-drop list dynamic and the easiest one is converting the source data into an Excel Table.

Select the cells containing data > go to the Insert tab in the Excel Ribbon > Table > Click OK

Dynamic drop-down list in Excel

Ctrl +T‘ is the keyboard shortcut to create Table in Excel

After converting the data into Excel Table, I have named it as ‘Food’.

Dynamic drop-down list in Excel

To use this table as source data of a drop-down list,

Select the cells where you want the drop-down list > Activate the Data Validation dialog > Select List from the Validation criteria > Type in the following formula in the input box for Source

=INDIRECT("Food[Desserts]")

‘Food’ is the name of the Table and ‘Desserts’ is the column header

Dynamic drop-down list in Excel

Once you create a drop-down list like this, you can simply add items to the end of the source data and the drop-down list will update automatically.

Dynamic drop-down list in Excel

Read about Excel Tables

How to create a Dependent Drop-down list in Excel