How to create a drop down list in MS Excel – data validation tutorial

MS Excel is essentially a tool to enter, store and manipulate data. A key challenge of this is ensuring the right data is in place for you to manipulate. There is nothing worse than seeing the error message and knowing somewhere incorrect data has been entered which has thrown out all of your calculations.

Excel has already thought about this and given you a function to prevent just this sort of thing happening. It is the drop down list function and is provided within the Data Validation area. The drop down list can save you loads of time if you want people to select from a specific list of options and prevent the dreaded error messages.

Essentially the drop down list provides a predetermined list of options to be entered into a cell. This increases speed and removes the chance of misspellings or using the wrong data altogether.

There are several ways to create a drop down list using the data validation function in excel:

1. Highlight the cells that you want the drop down list to be displayed in
2. Using the ribbon select Data / Data Validation.
3. Settings tab should already be displayed
4. In the allow drop down list select ‘List’
5. Ensure that you have selected ‘In Cell Dropdown’
6. In the source drop down list enter the options that you want to appear in the list and separate the options with a comma. E.g. if you wanted your drop down list to cover fruits you could enter Apple, Pear, Banana, Peach. This would allow you to select from Apple, Pear, Banana, Peach in your drop down list.
7. When you click OK, these options will be provided in the cell.

The above method is useful if you only have a short list of options. If you wish to provide many options in your drop down list it would be more suitable to store the options within cells in your sheet and reference those cells to provide your data. This makes it easier to update the list in future and allows several drop down lists to be attached to one range of data.

1. Type the options that you want to appear in your drop down list into cells within the sheet. You may wish to hide these off the screen in an area of the sheet not used.

2. Follow steps 1-5 above

3. In the source box click the red arrow to the right of the box

4. This will take you back to your sheet.

5. Select the range containing the data that you entered earlier and hit enter.

6. Now click ok.

7. Now when you click in your drop down list it will contain the options within the range that you selected on your sheet.

A problem with the above method in Excel 2007 and below is that you are unable to reference a range on another sheet. However there are several approaches to getting around this.

Method 1

1. Follow steps 1-2 above.

2. Within the source box type the cell range that you wish to reference. It will be something like ‘Sheet2!D14:D18’. The first part is the sheet name followed by an exclamation mark, the second part is the range of cells in that sheet.

3. Follow steps 6-7 above.

Another method involves defining a named range for the cells containing your options for the drop down list.

1. Follow step 1 above.

2. Highlight the cells containing your data for the drop down list

3. In the top left of the sheet just below the ribbon there is a box containing the name of the cell you have selected. Here you can type a name for the range you have just selected.

4. Type a name that is meaningful e.g. ‘Fruit’.

5. Now go back to the main sheet and highlight the cells that you want your drop down list to appear in.

6. Click Data / Data Validation from the Ribbon.

7. In the source box enter the name of the range you have just identified e.g. ‘=Fruit’. It is important to have the equals sign before the named range.

8. Click OK

9. The data defined within that named range ‘Fruit’ will now appear in your drop down list.

Drop down lists are an extremely useful element of MS Excel and it is well worth you familiarising yourself with the above approaches.