Disclaimer: Creating a table is only available in Excel 2007 and newer.

In Excel, it’s possible to create a dropdown list within a cell. This allows users to choose from a list rather than having to type any information directly into a cell, maybe incorrectly. The simplest way to do this using the Data Validation options is pointing the source of the list to some cells that having the information in them. So, if you want the list to be comprised of departments for your company, you can make the source point to some cells else where that have the departments listed. The downside to this way is that if the list is visible to the user and not protected, they can edit or delete the list, probably by accident, but it can happen. Also, if you want to add new departments to this list, you will have to edit the source of the list to include them.

Setting Up A Simple Data Validation List Without Using A Table

1. Create a list of 5 departments in cells E1, E2, E3, E4, and E5
2. Select cell A1
3. On the Ribbon, select the Data tab
4. In the Data Tools group, click on Data Validation
5. On the Settings tab, from the Allow dropdown list, select List
6. In the Source textbox, type =E1:E5
7. Click OK
8. Verify A1 contains a dropdown list to select Departments

Setting Up A Data Validation List Referencing A Table Column

First, we have to create the table with the departments in it. We will do this on a separate worksheet.

1. Create a new worksheet by clicking on the Insert Worksheet button to the right of the very last worksheet tab at the bottom of the screen. (Shortcut Shift+F11)
2. Rename this worksheet Administration by right clicking on the worksheet tab and selecting Rename
3. In cell A1 of the new worksheet, type Name
4. Type the rest of the departments below in cells A2, A3, A4, A5, and A6
5. Select cell A1
6. To convert this range to a table, we need to select the Insert tab on the Ribbon
7. In the Table group, click on the Table button
8. Double check to make sure the range =$A$1:$A$6 is in the textbox
9. Check the My Table Has Headers checkbox
10. Click OK

Next, we need to change the name of this table to something more descriptive.

1. Click somewhere within the table you just created
2. On the Ribbon, select the Table Tools Design tab
3. In the Properties group, click inside the text box that has the default table name, usually Table1, or something similar.
4. Type Departments in the textbox, replacing the default name
5. Press Enter on the keyboard

Now that the table is setup with a descriptive name, you can reference the table using this name. You can also take it a step further and reference a specific column inside the table. The benefit of something like this, is that when you add new rows to the table, any formula using the table reference will update to include the new rows automatically.

To reference a specific table column you can use the following method.

TableName[ColumnName]

In our example, you would use the following.

Departments[Name]

Now we need to go back and setup our Data Validation using the table reference.

1. Go back to the blank worksheet that we started with before making the Administration worksheet
2. Select cell A1
3. On the Ribbon, select the Data tab
4. In the Data Tools group, click on Data Validation
5. On the Settings tab, from the Allow dropdown list, select List
6. In the Source textbox, type =INDIRECT(“Departments[Name]”)
7. Click OK
8. Verify A1 contains a dropdown list to select Departments

Microsoft doesn’t allow direct reference to a table and its columns from the Data Validation. Because of this, we have to include the INDIRECT function as well. The image below shows you the arguments dialog box which gives a brief description of the function.

Now that this is setup, you can add new rows to the Departments table, and they will automatically show up in the Data Validation dropdown list.

Download Example

Excel Data Validation List Examples