Creating a Dynamic Drop-Down List Using Data Validation in Excel

This Data Validation in Excel tutorial is suitable for users of Excel 2013, 2016, 2019, and Word for Microsoft 365. 

Objective 

Create a data validation drop-down list that dynamically updates when new items are added.  

Dynamic Data Validation Lists Explained 

Data Validation in Excel is a useful feature that allows you to control the type of data that is going into the spreadsheet. It helps reduce errors and maintain the integrity of the spreadsheet.  

There are many different types of data validation options you can apply, but one of the most popular is the drop-down list. A drop-down list displays items of your choice and lets the user select an item from the list. This means the data going into the cell is controlled, and free from interference or spelling errors!

In this example, I am using a spreadsheet that has been setup to log invoice details. I have created a data validation drop-down list to show the list of clients. Selecting a client from the list as opposed to manually typing it into the cell, is a lot more accurate and efficient.  

The drop-down list was created from a master list of clients I have on a separate spreadsheet.  

By default, data validation drop-down lists are not dynamic. This means that whenever I get a new client and add it to my ‘Clients’ spreadsheet, I would need to create my data validation drop-down list all over again to include the new entry. This is time-consuming, a bit clunky, and not very convenient.  

This is where a dynamic data-validation drop-down list can be useful.  

If we make the list dynamic, whenever we add a new client, the drop-down list will automatically update to include the new client.  

Creating a Dynamic List 

There are a few different parts to this, but we need to start by making the list of clients dynamic. We do this by putting the list in a table and then creating a named range to specify the cell range we want to use in the drop-down list.  

1. Click on any cell within the Client List. 

2. Press CTRL+A to select all. 

3. On the Home tab, in the Styles group, click Format as Table. 

4. Select any table style from the options. 

The Format as Table dialog box will appear. Ensure that the correct data range is selected. If your data contains headings, ensure there is a tick in the ‘My table has headers’ box.  

5. Click OK. 

The data is now contained within a table. Name the table.  

6. On the Table Design tab, in the Properties group, click in the Table Name field. 

7. Enter a name for the table ensuring there are no spaces, and press Enter. 

In this example, the table only has one column, but you may have a table that contains many columns. To create a dynamic list, you need to specify which column of the table contains the information to be used in the drop-down list by naming the range of cells.  

I want Company A to Company H to appear in my drop-down list. 

8. Select cells A2 to A9. 

9. Give the cell range a name by typing it into the Name Box just above. 

Setting up the Data Validation List 

It is now time to set up the data validation list.  

1. Go to the worksheet and click in the cell where you want the drop-down list to appear. 

2. On the Data tab, in the Data Tools group, click Data Validation. 

3. In the Validation criteria section, click the drop-down arrow underneath Allow and select List. 

We are now going to tell Excel to use the named range we created to populate the list.  

4. In the Source box type the = sign. 

5. Press the F3 key. 

NOTE: The F3 key is a shortcut that will display any named ranges that have been created in the workbook to make them easier to select.  

6. Select the named range. 

7. Click OK. 

8. Click OK again. 

There will now be a drop-down arrow in the selected cell, which will display a list of the clients.  

Updating the List 

The beauty of setting up the drop-down list this way is that when new items are added to the master list, in this case, the client list, the drop-down list will automatically update to include the new entry.  

In this example, I have added two new clients ‘Company I’ and ‘Company J’ to the Client List, and because the Client List is a table, it automatically expands to make the new entries part of the table.  

The new entries will automatically be included in the drop-down list.  

To read more about creating dynamic data validation lists in Excel as well as other methods of data validation, check out the following links: 

Excel Campus – How to Add New Rows to Drop-down Lists Automatically – Dynamic Data Validation Lists 

Extend Office – How to Create Dynamic Data Validation and Extend the Drop Down Automatically? 

ExcelJet – Excel Data Validation Guide 

Video Tutorial 

To see a demo of how to create dynamic data validation lists in Excel, please watch the following video tutorial.  

Creating Dynamic Data Validation Lists in Excel Video (9:47) 

For more Free Excel tutorials from Simon Sez IT. Take a look at our Excel Resource Center.

To learn Excel with Simon Sez IT. Take a look at the Excel courses we have available.