Drop Down List via Data Validation

This Excel Tip will explain how you can create a drop-down list within a cell using data validation like the example shown in the image below (Fig 1). You can also use the Combo Box form field to create a drop down list, but this article is specifically about the data validation approach.

Fig 1. Drop Down List via Data Validation

Hint: One of the reasons I prefer the data validation approach is that the list will work correctly when you open the file in OpenOffice. If use use a Combo Box, you have to redefine the linked cell after you open it with OpenOffice. Perhaps in the future, this will change, but for now ...

There are two main ways of creating your drop-down list. The first is to simply enter the list manually in the data validation dialog box as follows:

1. Select the cell, or range of cells, where you want to add the drop-down list.
2. Go to Data > Validation > Settings tab (see image below)
3. Select "List" from the Allow: drop-down box
4. Enter your list in the Source: field using a comma to separate the items

Entering a list in the Data Validation dialog box

The problem with this approach is that if you use this in a lot of cells and later want to update the list, you have to do so in every single cell and there is a good chance you'll miss one. There is a much more elegant approach ...

Defining a Drop-Down List using a Range

Instead of manually entering the list of items in the data validation dialog box, you can reference a range of cells. For example, let's say I have a separate worksheet (Sheet2) with my list defined in cells A1:A3 as shown below. If I want to create a drop-down list within a cell on a different worksheet, I'll need to create a Named Range (Insert > Name > Define) that references this list. In this case, I've named the range "myList". You can later hide the worksheet containing your list to keep your workbook looking nice and clean.

In the data validation dialog box, instead of entering the list manually, you enter a reference to the named range in the Source: field as shown below:

If the validation range is on the same worksheet as your drop-down list, then you can use a reference like "=A1:A3". The named range approach is the only way I know of to get around the following error:

Note: When using a named range for a data validation list, the named range must be defined as a reference to a range of cells. If you're thinking of getting fancy and want to define a name without a cell reference, and succeed, please let me know how you did it.