Excel’s data validation feature lets you embed a drop-down list into a sheet. You can populate that list in many ways, but a static list is probably the easiest. If you update the list frequently, the feature’s list is inadequate. Instead, take advantage of Excel’s table (.xlsx format only) object. If the validation control references a table, you can update the list (in the sheet), and the validation control will update automatically!

In a nutshell, you’ll enter list items into a sheet and convert the list to a table. Then, you’ll assign a defined (range) name to the table. When you create the validation control, you’ll reference the defined name (which references the table). Throughout this article, we’ll work with the .xlsx table object. You can work with any simple list or download the .xlsx demonstration file. This technique doesn’t easily make the transition to Excel’s earlier .xls format. If we get enough requests, I’ll cover an Excel .xls format in a subsequent column.

The list items

First, you need a list — we’ll use the one shown in Figure A. You’ll notice that the list is in alphabetic order. Maintaining an alphabetized list adds a new problem to the technique, but we’ll discuss that later. For now, you just need a list — alphabetized or not.

Figure A

Enter a list.

The table

The next step is to convert the list to a table (Excel 2007, 2010, and 2013):

  1. Click any cell inside the list.
  2. Click the Insert tab.
  3. Click the Table option in the Tables group.
  4. In the resulting dialog, check the My Table Has Headers option (shown in Figure A above) because the example does. Yours might not, so check the appropriate option.
  5. Click OK.

The resulting table has many advantages and a few disadvantages. You can learn more about tables by reading “10 reasons to use Excel’s table object.” For our purposes, the feature eliminates a lot of work and its inherent limitations aren’t relevant.

The defined name

Once the table is in place, assign a defined (range) name to it. Excel assigns a defined name (of sorts) to the table object, but you can’t reference it directly in a data validation control.

While it isn’t necessary for this technique to work, I recommend giving table objects meaningful names, because they’re easier to work with. To name the new table, click anywhere inside the table and click the contextual Design table. In the Properties group, you’ll see Excel’s default name — Table1, Table2, and so on. Click inside that control and enter a new name, as shown in Figure B.

Figure B

Give the table a meaningful name.

Now you’re ready to assign a defined name to the table, as follows:

  1. Click the Formulas tab.
  2. In the Defined Names group, click Define Name.
  3. In the resulting dialog, enter a name for your list.
  4. Then, enter the table’s name using the format =Tablename[columnheader]. In this case, that’s =Listofnatives[Natives], as shown in Figure C.
  5. Click OK.

Figure C

Reference the table to assign a defined name.

Figure D shows the worksheet’s two defined names — the Listofnatives table and Natives. It seems a bit redundant, but it’s necessary because you can’t refer to the table directly using the validation feature (at least, I haven’t found a way).

Figure D

The workbook has at least two defined names.

The validation control

Now you’re ready to add the validation control. This feature has a lot to offer, but we’ll stick with the basics for now:

  1. Select a cell and click the Data tab.
  2. Click Data Validation in the Data Tools group.
  3. In the resulting dialog, choose List from the Allow drop-down menu.
  4. Enter the defined name, Natives, in the Source control (as shown in Figure E).
    Figure E
  5. Click OK. The resulting list is shown in Figure F.
    Figure F

To update the list in the validation control, add (or delete) an item to the Natives table. When you do, Excel updates the validation control automatically, as you can see in Figure G.

Figure G

The validation list updates when you add an item to the table.

A bit more on updating

To update the table, position the cursor in the table’s last cell and press [Tab]. Excel will automatically add a new row to the table. Or, you can insert a new row anywhere in the table — this is helpful if you’re maintaining an alphabetized list. If you want to automate the alphabetizing sort, you can use the macro shown in Listing A. After adding a new item to the bottom of the table, execute the macro before using the validation drop-down.

Listing A

Sub SortListofNatives()
' Keyboard Shortcut: Ctrl+s
With ActiveWorkbook.Worksheets("TableMethod").ListObjects("Listofnatives")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("A1")
With .Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End Sub

It’s a bit of a nuisance to remember, and I don’t recommend this route in a distributed workbook, because users won’t remember to execute the macro first. If you need this much flexibility, you’ll need to find a way to automate the sort macro.

Send me your question about Office

I answer readers’ questions when I can, but there’s no guarantee. When contacting me, be as specific as possible: For instance, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. I’m not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.