Excel: Create a drop-down listMay 17, 2009
I learned a new trick in one of Mike Hughes’ sessions at the WritersUA Conference in March — how to create a drop-down list of valid values in Excel.
Now, for all you Excel geeks out there, this is probably no-brainer stuff to you, but I’m an Excel novice so I was really delighted to learn how to do this. And it’s really very simple. Here’s what you do in Excel 2003:
- Go to a blank area of your spreadsheet, outside the table.
- Enter the items for your list in a single column (no blank cells).
- Sort the list, if required.
- Select the cell in your table where you want these items to show as a drop-down list.
- From the menu, select Data > Validation. The Settings tab displays by default.
- Select List from the Allow drop-down list.
- Click inside the Source field.
- Select the cells that contain your list. This range will display in the Source field as something like this: =$H$5:$H$10.
- Click OK. The table cell now has a drop-down arrow next to it, and you can now select any item in your list.
- If you want to make this list of values available to other cells in the same column, click in the cell, then click and drag the bottom right + of that cell down to the cells below it. This will apply the same drop-down list to those cells too.
You can store the list of values on any worksheet (handy if you have multiple lists you need to maintain), or in a different spreadsheet. Check Excel’s online Help for how to reference these other locations (search under ‘Create drop-down list’).
[Link last checked April 2009]