Boost Your Microsoft Excel Skills: Create Dynamic Data Validation Dropdown Lists Using Tables

Data validation lists in Excel are essential for ensuring data consistency and accuracy. By leveraging Excel Tables, you can create dynamic drop-down lists that automatically update as new data is added. This approach minimizes manual updates and enhances data integrity.

Setting Up A Simple Data Validation List Without Using A Table

Create a list of 5 departments in cells E1, E2, E3, E4, and E5
Select cell A1
On the Ribbon, select the Data tab
In the Data Tools group, click on Data Validation
On the Settings tab, from the Allow dropdown list, select List
In the Source textbox, type =E1:E5
Click OK
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.

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)
Rename this worksheet Administration by right clicking on the worksheet tab and selecting Rename
In cell A1 of the new worksheet, type Name
Type the rest of the departments below in cells A2, A3, A4, A5, and A6
Select cell A1
To convert this range to a table, we need to select the Insert tab on the Ribbon
In the Table group, click on the Table button
Double check to make sure the range =$A$1:$A$6 is in the textbox
Check the My Table Has Headers checkbox
Click OK

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

Click somewhere within the table you just created
On the Ribbon, select the Table Tools Design tab
In the Properties group, click inside the text box that has the default table name, usually Table1, or something similar.
Type Departments in the textbox, replacing the default name
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.

Go back to the blank worksheet that we started with before making the Administration worksheet
Select cell A1
On the Ribbon, select the Data tab
In the Data Tools group, click on Data Validation
On the Settings tab, from the Allow dropdown list, select List
In the Source textbox, type =INDIRECT(“Departments[Name]”)
Click OK
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.