How to Add Dynamic Dropdown lists in Excel — 4 Easy Steps
This Dynamic Dropdown Lists in Excel tutorial is suitable for users of Excel 2013, 2016, 2019, and Word for Microsoft 365.
Objective
Create a simple Excel dynamic drop down list using data validation that continuously updates when new items are added.
This guide covers:
Video Tutorial – Dynamic Dropdown Lists in Excel
To see a demo of how to create dynamic data validation lists in Excel, please watch the following video tutorial.
Dynamic Dropdown Lists in Excel – Overview
Have you ever felt annoyed & frustrated when some of your Excel databases have invalid entries in their rows? We can definitely relate. It takes so much time & effort to clean up rows after rows of data, isn’t it? Well, we have data validation in Excel which solves this problem.
Related:
Importing And Cleaning Data In Excel
Sumif With Multiple Columns – The Sumifs Function In Excel
Using The Let Function In Excel
But wait! It has one fatal flaw. It is not dynamic; meaning whenever you need to make a new type of entry to the Excel drop down list, you need to start over & freshly create another drop down list.
4 Easy steps to have Dynamic Dropdown lists in Excel
But there is a better way to accomplish this easily. Let me introduce you to dynamic dropdown lists in Excel. We’ll take a step by step approach to elucidate what Dynamic Dropdown lists are all about.
Let’s look at each of these aspects in detail below. Before we jump into exploring Excel dynamic drop down list, first a quick recap about Excel data validation.
Data Validation in Excel.
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 dynamic dropdown lists option. An Excel 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 enabled Excel 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, a data validation-enabled Excel drop down list is 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-enabled Excel 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 an Excel dynamic drop down list can be very useful.
If we make them as dynamic dropdown lists, whenever we add a new client, the drop down list will automatically update to include the new client.
Creating a Dynamic Dropdown 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 dynamic dropdown lists.
- Click on any cell within the Client List.
- Press CTRL+A to select all.
- On the Home tab, in the Styles group, click Format as Table.
- 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.
- Click OK.
The data is now contained within a table. Name the table.
- On the Table Design tab, in the Properties group, click in the Table Name field.
- Enter a name for the table ensuring there are no spaces, and press Enter.
Also Read:
The Excel Solver Add-in: Five Minute Tutorial
Dynamic Array Functions In Excel (Xlookup, Filter, Unique, Xmatch): Webinar Recording
Creating An Excel Dashboard In 5 Minutes
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.
- Select cells A2 to A9.
- 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.
- Go to the worksheet and click in the cell where you want the dynamic dropdown lists to appear.
- On the Data tab, in the Data Tools group, click Data Validation.
- 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.
- In the Source box type the = sign.
- 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.
- Select the named range.
- Click OK.
- 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 Dropdown 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 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 Excel drop down list. Clever isn’t it?
That’s all folks. This is the easiest way to create an Excel dynamic drop down list. Keep visiting for more in depth quality articles about advanced Excel features & Excel formulas
Suggested Reads:
Dynamic Arrays In Excel: Eight, Must-know Formulas
Using The Let Function In Excel
To read more about dynamic dropdown lists as well as other methods of data validation in Excel, 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
FAQs
What is a dynamic drop down list in Excel?
“Dynamic dropdown lists” is the easiest way to implement data validation with the option to expand the list later, without changing the original data.
How do you validate dynamic data?
In Excel, you can validate dynamic data using a dynamic drop down list. This is done by selecting the source for the drop down list as the range where new dynamic data will be added later.
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.
Jenny Ann Valenciano
