h1

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).
    excel_list01
  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.
    excel_list02
  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.
    excel_list03
  8. Select the cells that contain your list. This range will display in the Source field as something like this: =$H$5:$H$10.
    excel_list04
  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.
    excel_list06
  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]

3 comments

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

    -Adam


  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: