Drop-down lists are an extremely common feature in most of my popular Excel templates, including the meal planner, money manager, and many financial calculators. 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.

Drop Down List
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
Data Validation Drop-Down List
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.

Named Range

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:

Data Validation Referencing a Named Range

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:

Data Validation Criteria 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.

Update (2/26/14): We've created a new Customer List Template that explains how to copy the customer list worksheet into a spreadsheet, create a drop-down list showing customer names, and then add lookup formulas to display the information for the chosen customer.

Cite This Article

To reference this article from your website or blog, please use something similar to the following citation:

- Wittwer, J.W., "Drop-Down Lists via Data Validation" from Vertex42.com

LIKE THIS PAGE?

Follow Us On ...

       
Master Excel - Spreadsheet Tips Workbook

Create Dashboard Reports in Excel!