Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Add Dynamic Dropdown lists in Excel — 4 Easy Steps

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:

Table Of Contents
  1. Objective 
  2. Video Tutorial – Dynamic Dropdown Lists in Excel
  3. Dynamic Dropdown Lists in Excel – Overview 
  4. 4 Easy steps to have Dynamic Dropdown lists in Excel
  5. FAQs


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.  

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


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. 

  1. Data Validation in Excel
  2. Creating a Dynamic List
  3. Setting Up the data validation list
  4. Updating the list

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.  

Dynamic Dropdown lists in Excel
Data Validation in Excel – Drop Down Lists

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

Master List of Clients
Master List of Clients

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. 
Format your list as Table
Format your list as Table

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. 
Select the source for Table
Select the source for Table

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

Name your Table and select Convert to Range
Name your Table and select Convert to Range

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. 
Give the cell Range a Name
Give the cell Range a Name

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. 
Open the Data Validation window
Open the Data Validation window
  • 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. 
Select the named Range as the source for your drop down list
Select the named Range as the source for your drop down list
  • Click OK again. 

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

Your Dynamic dropdown list is ready
Your Dynamic dropdown list is ready

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.  

You can now Update the list by adding rows of data to the Table Range
You can now Update the list by adding rows of data to the Table Range

The new entries will automatically be included in the Excel drop down list.  Clever isn’t it?

The dropdown list is automatically updated
The dropdown list is automatically updated

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: 

The Excel Filter Function

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

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

How to Create a Venn Diagram in Excel? 2 Easy Ways

A Guide to Jira Issue Types [2022]

Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers

How to Create an Invoice from an Estimate in QuickBooks Pro 2017

Advanced PivotTables – 1 Hour Crash Course

How To Manage Users in QuickBooks Pro 2017

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)