Excel Drop Down Lists

Creating a drop down list in Excel enables you to control the value entered into a specific cell by limiting the choices to those on a pre-defined list.

A drop down list is simply a type of data validation.  That is, Excel validates that the entry in the cell matches an item from your pre-defined list.

It’s handy to know that drop down lists are one of many different validation rules available in Excel, but in this tutorial we’re only going to look at creating drop down lists (in Excel 2007 & 2010), as they’re one of the most common.

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 drop down 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 Data Validation window like this:

4)      From the ‘Allow:’ drop down list choose ‘List’.  Your Data Validation window 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 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.

Excel Drop Down List Advanced Options

• 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 Drop Down Lists: a dynamic drop down list is one that updates 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.

Creating dynamic drop down lists in earlier versions of Excel requires jumping through a few hoops.  The solution is to use the OFFSET formula, which is not straight forward, so I’ll cover it in another tutorial.  For now it’s probably quicker and therefore cheaper to go and buy Excel 2010!

Share the knowlege with your friends and colleagues. Click the icons below for Twitter, Facebook, Stumbleupon and many more.