Automating Data Validation Lists in Excel

By David Ringstrom, CPA
Working in an Excel spreadsheet can be somewhat like the Wild West  unless other provisions are made, users can enter any value in any cell. One way you can restrict users to a predefined set of values is by way of Excel's Data Validation feature. I'll explain how you can create in-cell drop-down lists, along with how to "future proof" the Data Validation list so that the feature won't require maintenance if you add additional items in the future. This technique also resolves an annoying problem in Excel 2007 where data validation lists cannot be placed on other worksheets.
Before we look at Data Validation, let's first establish our list. Let's say we want the user to choose a type of fruit. On the second worksheet of a blank workbook, create a list, such as shown in Figure 1. Once you do so, make the list into a Table in Excel 2007 and later, or a List in Excel 2003 and earlier. After you click on any cell within your list:
  • Excel 2007 and later  Choose Insert and then Table. Make sure that My Table Has Headers is selected and then click OK.
  • Excel:Mac 2011  On the Tables tab of the ribbon, click the arrow next to the New command and then choose Insert Table with Headers.
  • Excel 2003 and earlier  Choose Data, List, and then Create List.
Figure 1: Enter a few items on the second worksheet of a workbook.
A benefit of tables (and lists in Excel 2003 and earlier) is that if you add items to the bottom of the list, the table will expand automatically to encompass the new items. However, we can't use this self-expanding table or list directly with Data Validation. To do so, we must create a range name that encompasses all but the first row of the table. Select the second through last row of your range, and then:
  • Excel 2007 and later  On the Formulas tab choose Define Name.
  • Excel 2003 and earlier, or Excel  Mac 2011: Choose Insert, Name, and then Define.
Once the Define Name dialog box shown in Figure 2 appears, enter a name such as Fruit, ensure that the Refers to field references the second through last row of your table, and then click OK.
Figure 2: Assign a name to the second through last cell of your table or list.

Already a member? log in here.

Editor's Choice