Creating a drop-down list in Excel

To create a drop-down list you will first need to create a list of data – this list is normally created on another sheet and in many occasions, hidden, so that it is not tampered with.

To Create a List

Insert the data for your list – for good practice it is always recommended that you have an appropriate heading for the list, like the example below:

Capture

The list must have a defined name. To name a range of cells, select the range (in the example above, C2:C8) and go to the Formula tab/ Defined Names group. Select Define Name.

Capture5

Or alternatively, you can select the range of cells and insert the new name in the Name Box and press the Return key.

Capture3

To check your Named Cells you can click on the down arrow in the Name Box and the list of Named Cells should appear:

Capture4

Using the Named Range in a Drop-Down List

Highlight the range of cells that will require a drop down list – if you wish to apply the list to an entire column, select the column.

Go to the Data tab/ Data Tools group and select Data Validation.

Select List from the options available in the Allow section

Enter the name of the Named cell that contains the list in the Source box (must be an exact match).

If you are unsure of the name you can click on the ‘go to sheet’ button Capture7 and manually select the cell range.

Select OK.

Remove the drop-down list option from cells

Highlight the cell/cells. Go to the Data tab/ Data Tools group and select Data Validation.

Change the Allow setting to Any value

Select OK.

Click here to access PDF file

Print Friendly, PDF & Email

You may also like...

Skip to toolbar