Excel - Drop-down lists
Excel has a feature that allows you to format a cell so that it may only contain certain answers you have specified. These will be displayed as a drop-down list that displays when a cell with the formatting on is selected. This is useful for removing things such as spelling errors or typos and allows less chance for error whilst inputting data.
Creating a drop-down list
The first step in the process is to record all the options you would like available on the drop-down list. It's recommended that this list should be created in a new worksheet as if it's deleted later on the list will stop functioning. If you expect to have more than one type of list they can all be kept on the same worksheet away from the rest of your data on another worksheet. In this example, I will create a list of University departments to be input.
Create a column of your responses that you would like in your list (remember to place this in a new worksheet):
Once you are happy that the list is complete you should select the data being careful not to select the title of the list. Then right-click and select "define name":
You will then be asked to choose a name for the defined range and leave any comments about it too. You should also be able to see the area that's referred to by its cell reference:
Once you click ok, Excel will now refer to this list as the name you have set so remember it for the next step.
Applying conditional formatting to the cells you wish to have a drop-down list on
First, you will want to select all the cells you wish to see the drop-down list available on. This can be a row, column, single cell or group of cells:
Once selected you will find the 'data validation' button in 'data' tab of the menu ribbon:
In this menu, you will be able to set the allowed criteria for the cell/s you have just selected. We want to allow a list only, so select 'list' from the 'allow' drop-down. You will then want to reference the range we defined the name for previously. Typing in the 'source' field '=[Name of the list]', which in my example would be "=Departments" :
if you did not define a name for the range of data then you can also use the 'Source' section of this menu to reference an area containing this list. e.g. (=A3:A13)
Once 'Ok' is clicked, selecting a cell that's had the data validation applied to it, should result in a drop-down list is available:
