Create A Drop Down List In Excel
Visit our website to see a
video tutorial for creating a drop-down list in Excel.
Drop down lists are a great addition to a worksheet making data more efficient and also restricting what values can be entered into a cell. The procedure for creating a drop down list is fairly simple to follow.
There are 3 stages involved:
i) Creating a list of values you want to appear in the list – normally on a separate sheet in the same workbook
ii) Naming the list
iii) Using Data Validation to create the drop down list
1) Start with a new workbook which has at least two worksheets. Switch to Sheet2, this is where you will create your list of values. You may want to consider the order you enter the values as this will be the order they will appear in the drop down.
2) Enter your values, one value in each cell, working down a column. I’m going to list some UK cities, you might think of another list that relates more closely to what you do.
London
Sheffield
Manchester
Leeds
Brighton
Edinburgh
Cardiff
3) Now you are going to name your list. If you have not named cells or ranges previously I’ll give some explanation as to why we want to do this. Currently my list is in the range Sheet2!A1:A7. It’s going to be a lot easier to refer to this list later on if we give it a meaningful name as a substitute for this current range description.
How do we name this range? First thing to do is to select the list – I’ll assume you know how to do this: make sure each cell that contains a list value is selected. Now find the Name Box which is to the left of the formula bar. The Name Box will show the cell address of the first cell you selected in the range. In my example this would be A1. Click into the Name Box and the cell address gets highlighted; now you are ready to type the name you want to give your list. I am going to call my list cities. Once you have typed your name you must use the Enter key on your keyboard to confirm, otherwise the name will not be stored.
Names are not case sensitive but they do need to follow some basic rules:
- Names must start with a letter or an underscore but can contain a number
- Names cannot contain spaces but can contain underscores to separate words
- Names cannot contain any other characters other than underscores, letters or numbers
Switch to Sheet1 and click into the cell that you want the drop list to appear in. If you want the list to be available in more than one cell, select those cells as well. Now click Data > Validation. This opens the Data Validation dialogue box. For Allow: select List. Now click into the Source: box and press the F3 key on your keyboard. F3 shows the Paste Name box. Select the name you created from this box and click OK. The source box now reads =cities for my example. Click on OK in the Data Validation box and you are done: your cell will have a drop down list.
If you want to prevent users or even yourself entering any value other than a value from your list, select the cell and go back to Data > Validation. Then click on the Error Alert tab. The default Stop style error alert prevents the entry of values that are not in the list but also displays an error message if an invalid value is attempted. The Title and the Error Message appear in a box when a user enters such a value. In my example the Title might be City and the Error Message Sorry you must select a city from the list, your entry is not valid.
Click on OK and test you error message.
As a last thought you might want to think about hiding the sheet that holds the named list – Sheet2. That way things look a bit tidier and the list is likely to be inadvertently changed or deleted. Select the the Sheet2 tab and then click Format > Sheet > Hide.
For future reference to Unhide the sheet click Format > Sheet > Unhide.
