MS Excel 2010 Drop-down

How to create a dropdown in Excel 2010

drop-down list in Excel 2010

I have a similar post about making drop-down with Excel 2003, which has apparently become obsolete with the introduction of ribbon menu in Excel 2007/2010. Regardless of the changes, the general concept of creating a drop-down list in Excel 2010 is still the same as 2003: using Excel Data Validation List. The drop-down list is very useful in that it presents a limited number of options for users to select, users can’t enter their own input, thus limit potential errors and create more consistent output.

The Excel drop-down list has 2 parts: the drop-down options, and the list that constitutes the options — mostly hidden behind the scene. The list and the drop-down itself can be placed on the same worksheet, or different worksheets, or even on different workbooks; you just need to link the two things properly. In the example below (sample file at the end), I put the drop-down options in a separate worksheet named “Hidden.”

Create Excel drop-down list

Here’s how to create a drop-down list of months. In this example, the list and the drop-down are on different spreadsheets.

  • Create the list: Type your list in a certain column on Sheet2. In this example, cells A2:A12 hold your the list of months, and Sheet2 was re-named to “Hidden” to remind you that this should be set hidden at the end; other than that the name doesn’t do any good in this case.

    drop-down list in Excel 2010

  • Use Data Validation: In Sheet1, select the cell you want to display the drop-down — you can type in something like “Select month” or “Click to select month” — then go to the menu Data, select Data Validation from the ribbon. A dialog box will appear.

    drop-down list in Excel 2010

  • Use List: On the Settings tab, select List from the Allow drop-down > click on the highlighted red arrow > the Data Validation window pops up.

    drop-down list in Excel 2010

  • Select data source: Click on Sheet2/Hidden > drag to select the months > Click on the red arrow again to expand the Data Validation popup > Hit OK and you’re done.

    drop-down list in Excel 2010

drop-down list in Excel

Now if you click on “Month” you’ll see a down arrow button, which will expand the drop-down list of months.

You can download the sample file here: Sample Excel 2010 drop-down

Hope this helps!

Related Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *