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:
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.
Or alternatively, you can select the range of cells and insert the new name in the Name Box and press the Return key.
To check your Named Cells you can click on the down arrow in the Name Box and the list of Named Cells should appear:
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 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.