Excel Tables have many great features to them and one of them is their ability to create a dynamic drop down list.

A dynamic drop down list expands as the Excel Table expands when new data gets added to it.

This is great when you want to have users select from a predefined text or value list rather than having them manually enter data, which can lead to mistakes.

Here is how to create a dynamic data validation list using Excel Tables:
STEP 1:  Convert your list in to an Excel Table (Ctrl+T)

STEP 2: Select your Table column by hovering over the Excel Table and left clicking when the arrow pointer shows

STEP 3: In the ribbon menu, go to Formulas > Define Name > enter a custom name with no spaces (we will put this name in step 5) and press OK

defined name list

STEP 4: Click on the other Excel Table column that you want to enter the dynamic list into and go to the ribbon and choose Data > Data Validation > List

STEP 5: In the Source box enter the name you created in Step 3 and press OK or click in Source box, press F3 and select the named range from there (thanks to Michael from www.excelpogodzinach.pl for introducing this tip to us!)

data validation

STEP 6: If you want to add extra data into your Excel Table list, hover with your mouse in the bottom right hand corner and when you see a double arrow, drag down.

STEP 7: Enter a new entry and this will automatically be updated in your drop down list

DOWNLOAD EXCEL WORKBOOK

Dynamic Data Validation

HELPFUL RESOURCE:

Zack Barresse Excel Tables Podcast

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Lock & Protect Formula Cells If you have a workbook with lots of formulas and you want to protect those formulas from being amended by other people who share your workbook, then you can! You need to follow these steps: 1. Press the Go To Special shortcut CTRL+G 2. Select the Constants box and press ...
Show The Percent of Parent Total With Excel Pivot ...   Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF PARENT TOTAL calculation. In the example below I show you how to get the Percent of a Product Total i.e The % Sales of each Product ...
Text To Columns: Dates Whenever you download data from an external ERP system like Oracle, SAP, etc, you can have data that is not formatted the way you and Excel likes. Sometimes "Date" values are downloaded as "Text", so you cannot sort in the periodic date format. No worries!  Text to Columns ...
Split First & Last Name Using Text to Columns There are times when you receive a data set of employee full names in one column and you want to separate the full name into first name and surname in separate columns. One way is to use the Power Query method, which is great if you have lots of data that gets added each day, ...