Excel: Create a drop-down list

May 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:

  1. Go to a blank area of your spreadsheet, outside the table.
  2. Enter the items for your list in a single column (no blank cells).
  3. Sort the list, if required.
  4. Select the cell in your table where you want these items to show as a drop-down list.
  5. From the menu, select Data > Validation. The Settings tab displays by default.
  6. Select List from the Allow drop-down list.
  7. Click inside the Source field.
  8. Select the cells that contain your list. This range will display in the Source field as something like this: =$H$5:$H$10.
  9. Click OK. The table cell now has a drop-down arrow next to it, and you can now select any item in your list.
  10. 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.
    excel_list08 excel_list09

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]


  1. Hey Thanks for your help. Your excel expertise really helped me with my small project.


  2. Thanks a lot! It’s good idea to save our time during the developing projects.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: