Creating an Excel drop down list, or Data Validation List as they're formally known, in Excel enables you to control the value entered into a specific cell by limiting the choices to those on a pre-defined list.
Excel validates that the entry in the cell matches an item from your pre-defined list, or you can simply click on the down arrow to select from an item in the list.
Download the workbook and follow along.
How to create an Excel drop down list
|1) You first need to define the items that will appear in your list. To do this simply list the options in any column in your worksheet, preferably with no blank spaces between rows and no duplicates.|
2) Select the cell or range of cells you want validated.
3) Go to the Data tab of the ribbon in the Data Tools section click on ‘Data Validation’ and select ‘Data Validation’ from the list. You’ll get a dialog box like this:
4) From the ‘Allow:’ list choose ‘List’. Your dialog box should now look like this:
Make sure ‘In-cell dropdown’ is checked.
5) In the source box enter the cell range that your list is in. Note: the cell range should be absolute. That way if you copy the data validation to any other cells the reference remains on your list. If you aren’t sure how to make the reference absolute, let Excel do it for you by clicking in the Source box, then with your mouse, highlight the range of cells containing your list on your worksheet. Excel will enter the range in the correct format.
6) On the ‘Input Message’ tab you can enter a message that will appear when the user selects the cell. This is to give the user guidance as to what you want them to do. For example, mine simply says, ‘Please Pick From List’. Note: if you enter a title it will also appear in the message, so I’ve left mine blank....in this case I think saying it once should be enough 😉
7) The last tab is ‘Error Alert’. Here you can define the message you want displayed if the user tries to enter something that isn’t on the list.
There are three styles of Error Alert you can choose from. Stop, as you see above, Warning, or Information. Simply pick the one you want from the ‘Style’ list.
That's it. You're good to go.
- Referencing drop down lists on another worksheet: you might like to keep all your reference data on a separate ‘Legends’ worksheet to keep your workbook tidy.
To do this, in the 'Source' field you will need to add the sheet name in front of the cell range of your list (with the sheet name enclosed in inverted commas and with an exclamation mark at the end).
For example, instead of my reference being =$C$1:$C$7 it would be =’Legends’!$C$1:$C$7. Unfortunately you have to type this into the Source box, you can’t use your mouse to select the sheet with the cells containing your list as described in step 5 above.
- Creating Dynamic Lists: These update when items are added or removed from your Source. Make sure you do this if your list is likely to change. There's no point doing more work than you need to.
In Excel 2007 onwards this is easy to do. Simply format your list as a Table and then give the Table a Named Range. To do this,
A. Highlight your list
B. From Insert tab, select Table, click OK.
C. Now give the table a Named Range. I’ll call mine ‘ListPrograms’.
D. Now in the 'Source' type in the name of your range. In my case =ListPrograms as we did in Step 5.
Please share the knowlege with your friends and colleagues on Twitter, Facebook, Google+, LinkedIn etc.